Lookup in Excel
Lookup in Excel.
📘 What is Lookup in Excel?
Lookup refers to the process of searching for specific data in a dataset or table and returning corresponding results.
It is especially useful when working with large tables where you need to find information quickly based on a known value.
📊 Example:
You have a product ID and want to find its price, category, or availability.
🔄 Types of Lookup Functions in Excel.
There are several functions available in Excel for lookup purposes:
| Function | Direction | Best For |
|---|---|---|
| LOOKUP | Vertical/Horizontal | Basic searches |
| VLOOKUP | Vertical | Table with key in first column |
| HLOOKUP | Horizontal | Table with key in first row |
| XLOOKUP | Vertical/Horizontal | Modern and flexible (Excel 365) |
| INDEX + MATCH | Vertical/Horizontal | Dynamic & powerful combo |
VLOOKUP in Excel.
📘 What is VLOOKUP?
VLOOKUP stands for Vertical Lookup. It is a built-in Excel function used to search for a value in the first column of a table and return a corresponding value from a different column in the same row.
📊 It is especially useful when working with structured data such as:
Student records
Product inventories
Employee databases
🧩 Basic Syntax of VLOOKUP
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Explanation of Arguments:
| Argument | Description |
|---|---|
| lookup_value | The value you want to search for. |
| table_array | The range of cells that contains the data. |
| col_index_num | The column number from which to retrieve the result (1 = first column). |
| [range_lookup] | Optional. TRUE = approximate match, FALSE = exact match (recommended). |
🔍 VLOOKUP with Cell References.
Instead of hardcoding values, you can use cell references:
=VLOOKUP(E1, A2:C5, 3, FALSE)
✅ When to Use FALSE vs TRUE.
| Match Type | Description |
|---|---|
| FALSE | Exact match (most common) |
| TRUE | Approximate match (requires sorted data) |
💡 VLOOKUP Tips & Best Practices
Always use
FALSEfor exact matches to avoid errors.Use Absolute References (like
$A$2:$C$5) to lock your table range when copying formulas.If data might not be found, use:
=IFERROR(VLOOKUP(...), "Not Found")
🔁 Dynamic VLOOKUP Using Drop-down (Data Validation)
Create a drop-down list with Product IDs using Data Validation.
Use
VLOOKUPto fetch the product name or price when a user selects a product from the drop-down.
This is great for interactive dashboards or user forms.
🧱 Common Errors and Troubleshooting.
| Error Type | Cause | Fix |
|---|---|---|
| #N/A | Lookup value not found or doesn't exist | Use IFERROR() to handle |
| #REF! | Invalid column number in formula | Check if column number exists in the table range |
| Incorrect result | Range not fixed with $ | Use absolute references like $A$2:$C$10 |
| Value not found | Data mismatch (e.g., text vs number) | Ensure data types match |
📘 HLOOKUP in Excel.
🔍 What is HLOOKUP?
HLOOKUP stands for Horizontal Lookup. It is an Excel function used to search for a value in the first row of a table and return a value from a specific row in the same column.
Just like VLOOKUP searches vertically in columns, HLOOKUP searches horizontally in rows.
🔧 Syntax of HLOOKUP.
=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
📖 Explanation of Parameters:
| Parameter | Description |
|---|---|
| lookup_value | The value to find in the first row of the table. |
| table_array | The range of cells that contains the data. |
| row_index_num | The row number (starting from 1) from which to return the value. |
| range_lookup | Optional. TRUE for approximate match, FALSE for exact match (recommended). |
📌 How HLOOKUP Works
Searches for the value
"Science"in the first row (A1:D1).Finds the column where
"Science"is located.Moves down to the 3rd row (because
row_index_num = 3).Returns the value from that cell.
💡 Tips to Use HLOOKUP Effectively
Always use
FALSEas the fourth argument for exact matches.Fix your table range using
$if copying formulas:
=HLOOKUP("Math", $A$1:$D$3, 2, FALSE)
3. Use IFERROR to handle errors gracefully:
=IFERROR(HLOOKUP("History", A1:D3, 2, FALSE), "Not Found")
Rearrange data horizontally if needed — HLOOKUP only works across rows.
Ideal for cross-tab reports and monthly analysis arranged in rows.
🔎 XLOOKUP in Excel.
📘 What is XLOOKUP?
XLOOKUP is a powerful Excel function introduced in Excel 365 and Excel 2021. It allows you to search a range or array for a value and return a corresponding value from another range.
It’s designed to replace older functions like VLOOKUP, HLOOKUP, and even INDEX & MATCH.
✅ With XLOOKUP, you can:
Search vertically or horizontally
Lookup left or right (unlike
VLOOKUP)Get exact matches by default
Handle errors more easily
Return entire rows or columns
🧠 Why Use XLOOKUP?
Compared to VLOOKUP and HLOOKUP, XLOOKUP:
Is more flexible
Doesn’t require sorting
Doesn’t break when columns or rows are inserted or deleted
Can search in any direction
Supports error messages and multiple matches
🧩 XLOOKUP Syntax
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
Explanation of Arguments:
| Argument | Description |
|---|---|
| lookup_value | The value you want to search for. |
| lookup_array | The range or array to search in. |
| return_array | The range or array to return the matching value from. |
| if_not_found | Optional. What to return if no match is found. |