VLOOKUP & HLOOKUP
Learn how to find data in tables using VLOOKUP and HLOOKUP
VLOOKUP & HLOOKUP
These are two powerful functions that help you find information in a table.
Think of it like searching in a phone book. You know someone's name, and you want to find their phone number. VLOOKUP and HLOOKUP do exactly that in Excel.
What is the Difference?
VLOOKUP = Vertical Lookup. Searches DOWN a column. HLOOKUP = Horizontal Lookup. Searches ACROSS a row.
Most of the time, you will use VLOOKUP because data is usually organized in columns.
VLOOKUP: How It Works
You have a product list:
| A (ID) | B (Product) | C (Price) | |
|---|---|---|---|
| 1 | 101 | Apple | 50 |
| 2 | 102 | Banana | 30 |
| 3 | 103 | Orange | 40 |
You want to find: What is the price of product 102?
VLOOKUP will:
- Search for 102 in the first column
- Find it in row 2
- Return the value from the Price column
- Answer: 30
VLOOKUP Formula
=VLOOKUP(what_to_find, table, column_number, 0)
Let us break it down:
| Part | Meaning | Example |
|---|---|---|
| what_to_find | The value you are searching for | 102 |
| table | The range where your data is | A1:C3 |
| column_number | Which column has the answer (count from 1) | 3 (Price column) |
| 0 | Find exact match only | Always use 0 |
Complete formula: =VLOOKUP(102, A1:C3, 3, 0)
Result: 30
Practice: Find Product Price
EasyFind the price of product ID 2. Table is A1:C3, price is in column 3. Use 0 for exact match.
Step by Step: Using VLOOKUP
Problem: Find the price of product ID 102.
Step 1: What are you looking for? Answer: 102
Step 2: Where is your data table? Answer: A1:C3
Step 3: Which column has the answer? Count: A=1, B=2, C=3. Price is in column C, so: 3
Step 4: Write the formula
=VLOOKUP(102, A1:C3, 3, 0)
Practice: Find Employee Salary
EasyFind the salary of employee ID 102. Table is A1:C3, salary is in column 3. Use 0 for exact match.
HLOOKUP: How It Works
HLOOKUP is the same as VLOOKUP, but it searches horizontally (in a row).
You have data arranged in rows:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Product | Apple | Banana | Orange |
| 2 | Price | 50 | 30 | 40 |
You want to find: What is the price of Banana?
Formula: =HLOOKUP("Banana", A1:D2, 2, 0)
HLOOKUP will:
- Search for "Banana" in the first row
- Find it in column C
- Return the value from row 2
- Answer: 30
Practice: Simple HLOOKUP
EasyFind the price of Apple. Table is A1:C2, price is in row 2. Use 0 for exact match.
When to Use Which?
| Use This | When Data Is |
|---|---|
| VLOOKUP | Organized in columns (most common) |
| HLOOKUP | Organized in rows (less common) |
Common Mistakes
Mistake 1: Wrong column number You have 3 columns but you type 5. Excel will give an error.
Mistake 2: Forgetting FALSE Always add FALSE at the end. Without it, you might get wrong results.
Mistake 3: Search value not in first column VLOOKUP only searches the FIRST column of your table. Make sure your search value is there.
Summary
- VLOOKUP searches down columns:
=VLOOKUP(find, table, column, 0) - HLOOKUP searches across rows:
=HLOOKUP(find, table, row, 0) - Always use 0 for exact match
- Count columns/rows starting from 1
- Search value must be in the first column (VLOOKUP) or first row (HLOOKUP)
These functions save hours of manual searching. Practice them and you will use them every day.