5 Ways Excel Lookup

Introduction to Excel Lookup Functions

Excel lookup functions are a group of functions in Microsoft Excel that allow users to search and retrieve data from a table or range based on a specific value or criteria. These functions are essential for data analysis, reporting, and manipulation. In this article, we will explore five ways to use Excel lookup functions, including VLOOKUP, INDEX/MATCH, LOOKUP, HLOOKUP, and XLOOKUP.

1. VLOOKUP Function

The VLOOKUP function is one of the most commonly used lookup functions in Excel. It searches for a value in the first column of a table and returns a value in the same row from another column. The syntax for the VLOOKUP function is:

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Where:

• lookup_value is the value to search for
• table_array is the range of cells that contains the data
• col_index_num is the column number that contains the return value
• [range_lookup] is an optional argument that specifies whether to search for an exact match or an approximate match

For example, if you want to find the price of a product with the ID “ABC123”, you can use the VLOOKUP function like this:

=VLOOKUP(“ABC123”, A2:B10, 2, FALSE)

2. INDEX/MATCH Function

The INDEX/MATCH function is a combination of two functions that work together to perform a lookup. The INDEX function returns a value at a specified position in a range, while the MATCH function searches for a value in a range and returns its relative position. The syntax for the INDEX/MATCH function is:

INDEX(range, MATCH(lookup_value, range, [match_type]))

Where:

• range is the range of cells that contains the data
• lookup_value is the value to search for
• [match_type] is an optional argument that specifies whether to search for an exact match or an approximate match

For example, if you want to find the price of a product with the ID “ABC123”, you can use the INDEX/MATCH function like this:

=INDEX(B2:B10, MATCH(“ABC123”, A2:A10, 0))

3. LOOKUP Function

The LOOKUP function is a simple lookup function that searches for a value in a range and returns a value from the same position in another range. The syntax for the LOOKUP function is:

LOOKUP(lookup_value, lookup_vector, [result_vector])

Where:

• lookup_value is the value to search for
• lookup_vector is the range of cells that contains the data
• [result_vector] is an optional argument that specifies the range of cells that contains the return values

For example, if you want to find the price of a product with the ID “ABC123”, you can use the LOOKUP function like this:

=LOOKUP(“ABC123”, A2:A10, B2:B10)

4. HLOOKUP Function

The HLOOKUP function is similar to the VLOOKUP function, but it searches for a value in the first row of a table and returns a value in the same column from another row. The syntax for the HLOOKUP function is:

HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

Where:

• lookup_value is the value to search for
• table_array is the range of cells that contains the data
• row_index_num is the row number that contains the return value
• [range_lookup] is an optional argument that specifies whether to search for an exact match or an approximate match

For example, if you want to find the price of a product with the name “Product A”, you can use the HLOOKUP function like this:

=HLOOKUP(“Product A”, A1:E10, 2, FALSE)

5. XLOOKUP Function

The XLOOKUP function is a new lookup function introduced in Excel 2019 and later versions. It searches for a value in a range and returns a value from the same position in another range. The syntax for the XLOOKUP function is:

XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Where:

• lookup_value is the value to search for
• lookup_array is the range of cells that contains the data
• return_array is the range of cells that contains the return values
• [if_not_found] is an optional argument that specifies the value to return if the lookup value is not found
• [match_mode] is an optional argument that specifies whether to search for an exact match or an approximate match
• [search_mode] is an optional argument that specifies the search direction

For example, if you want to find the price of a product with the ID “ABC123”, you can use the XLOOKUP function like this:

=XLOOKUP(“ABC123”, A2:A10, B2:B10)

📝 Note: The XLOOKUP function is only available in Excel 2019 and later versions.

Function	Syntax	Description
VLOOKUP	VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])	Searches for a value in the first column of a table and returns a value in the same row from another column
INDEX/MATCH	INDEX(range, MATCH(lookup_value, range, [match_type]))	Searches for a value in a range and returns a value at a specified position in another range
LOOKUP	LOOKUP(lookup_value, lookup_vector, [result_vector])	Searches for a value in a range and returns a value from the same position in another range
HLOOKUP	HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])	Searches for a value in the first row of a table and returns a value in the same column from another row
XLOOKUP	XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])	Searches for a value in a range and returns a value from the same position in another range

In conclusion, Excel lookup functions are powerful tools that can help you to search and retrieve data from a table or range based on a specific value or criteria. By using the VLOOKUP, INDEX/MATCH, LOOKUP, HLOOKUP, and XLOOKUP functions, you can perform a variety of lookup operations and simplify your data analysis and reporting tasks. Whether you are a beginner or an experienced user, mastering Excel lookup functions can help you to work more efficiently and effectively with data in Excel.