📊 Demystifying Excel Lookup Functions: A Quick Guide 🧐

Image from freepik

Excel offers various lookup functions, each with its own strengths. Let’s break down the key differences using dummy data:

🔍 VLOOKUP: Vertical Lookup

Lets imagine you’re a librarian and need to find a book’s details by searching for its title in your database. VLOOKUP is your tool! It searches vertically (top to bottom) for a value and retrieves information from a corresponding column.

Syntax: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Dummy Data: Vlookup

Finding author of ‘Book B’ = VLOOKUP(“Book B”, A1:C4, 2, FALSE)

• lookup_value is "Book B."
• table_array is the range A1:C4.
• col_index_num is 2 because you want to retrieve data from the second column: Author (Similarly, if you want to get Genre for the same book the column index would be 3).
• range_lookup is set to FALSE for an exact match.

Result: The VLOOKUP formula will return the author of “Book B,” which is Author 2.

🔄 HLOOKUP: Horizontal Lookup

Now, imagine you’re a chef with a recipe book. You want to find a recipe’s cooking time by searching for its name horizontally. HLOOKUP is your sous chef! It searches from left to right and fetches data from a corresponding row.

Syntax: =HLOOKUP(lookup_value, table_array, row_index_num,[range_lookup])

Dummy Data: Hlookup

Finding time to cook ‘Pizza’ = HLOOKUP(“Pizza”, A1:D2, 2, FALSE)

• lookup_value is “Pizza.”
• table_array is the range A1:D2.
• row_index_num is 2 because you want to retrieve data from the second row.
• range_lookup is set to FALSE for an exact match.

Result: The HLOOKUP formula will return the cooking time of pizza which is 20.

🔗 INDEX MATCH: Dynamic Duo

What if your database is like a library card catalogue, with books scattered everywhere? INDEX MATCH is your superhero duo! Use the MATCH function to find the position (row or column) of a specific value in a lookup range. Then, you feed the result from MATCH into the INDEX function to retrieve the corresponding data from another range. This allows you to perform both horizontal and vertical lookups, exact or approximate matches, and search for values in rows or columns as needed.

Syntax: =INDEX(array, row_num, [column_num])

Match function can be used either to retrieve row_num or column_num or for both.

Syntax: =Match(lookup_value, lookup_array, [match_type])

Again, considering the case of first example, finding author of ‘Book B’ using index match =INDEX(B1:B4, MATCH(“Book B”, A1:A4, 0))

• MATCH(“Book B”, A1:A4, 0) finds the position of “Book B” in the range A1:A4, resulting in 3 (since it’s in the third row). Value of match_type is ‘0’ for an exact match.
• INDEX(B1:B4, 3) retrieves the value from the third row of column B, which is Author 2.

The formula returns the author of “Book B,” which is Author 2.

🔍 XLOOKUP: Excel’s Next-Gen Lookup Function 🚀

Traditional lookup functions like VLOOKUP and HLOOKUP are powerful, but they can be limiting. XLOOKUP is a powerful lookup and reference function introduced in Microsoft Excel 365 (Excel 2019 for Office 365 subscribers). It is designed to simplify complex lookup tasks, providing a more intuitive and flexible way to search for data in tables or ranges.

Let’s say you have a table of products, their prices, and the corresponding categories. You want to find the price of a specific product using XLOOKUP.

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

Dummy Data: Xloopup

To find the price of “Product B” launched in 2007 using XLOOKUP =XLOOKUP(“Product B”&2007, A1:A5&D1:D5, B1:B5, “Product not found”, 0, 1)

• lookup_value is “Product B” launched in 2007.
• lookup_array is the range A1:A5 where you’re searching for the product name with range D1:D5 for the specified year.
• return_array is the range B1:B5 from which you want to retrieve the price.
• “Product not found” is the optional value to return if no match is found.
• 0 specifies an exact match.
• 1 specifies a first-to-last search mode.

The formula will return the price of “Product B” launched in 2007, which is $20.

In the world of data analysis, having these lookup functions in your toolbox is like having a Swiss Army knife for various tasks.

So, whether you’re diving into VLOOKUP, HLOOKUP, INDEX MATCH, or embracing the future with XLOOKUP, remember that Excel’s lookup functions are here to help you unravel the mysteries hidden within your data.

References:

1. https://support.microsoft.com/en-us/excel
2. https://www.w3schools.com/excel