Day-5 Lookup in Excel

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

  1. Always use FALSE for exact matches to avoid errors.
  2. Use Absolute References (like $A$2:$C$5) to lock your table range when copying formulas.
  3. If data might not be found, use:
=IFERROR(VLOOKUP(...), "Not Found")

🔁 Dynamic VLOOKUP Using Drop-down (Data Validation)

  1. Create a drop-down list with Product IDs using Data Validation.
  2. Use VLOOKUP to 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

  1. Searches for the value "Science" in the first row (A1:D1).
  2. Finds the column where "Science" is located.
  3. Moves down to the 3rd row (because row_index_num = 3).
  4. Returns the value from that cell.

💡 Tips to Use HLOOKUP Effectively

  1. Always use FALSE as the fourth argument for exact matches.
  2. 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. 
				

				

				

				

					
				

				

				

				

					
				

				

				

				

					
				

				

				

				

					
				

				

					

		

					

		

				

		
		
			


	


	
		

		
Leave a Reply 
Your email address will not be published. Required fields are marked *