Mastering Excel Lookups (A Comprehensive Guide)

Ever found yourself staring at a massive spreadsheet, wondering how to find that one piece of information you desperately need? You must have thought, is there anything i still do not know? how do i get this done? well, i have felt that way when i interned at a data consulting firm and it’s okay if you don’t get it the first time. However, there are tools you can start out, which are easy to use once you get accustomed with them. It’s none other than the versatile Excel lookup functions! These powerful tools can help you navigate through your data like a pro, making your work more efficient and less frustrating. At the end of this post, a cheat sheet is made available for you below at the end of this article to download to help you master these tools. You’d really love it!

There different types of lookup functions you can use based on what you want to get from a data.
Here are 5 common lookup functions you can adopt:

• • Lookup

• • Vlookup

• • Hlookup

• • Index/match

• • Xlookup

LOOKUP is a simpler function that can be used for both vertical and horizontal lookups, especially when your data is sorted. It searches for the closest match to a given value in a sorted list. You give LOOKUP a keyword, and it searches for the closest match in a sorted list.

In a similar use case environment, We had a list of customers sorted by their last names. Using LOOKUP, we were able to quickly find a customer’s information based on their last name, even if the spelling was slightly incorrect.

---

Syntax:

=LOOKUP(lookup_value, lookup_vector, result_vector)

• • lookup_value: The value you want to search for.

• • lookup_vector: The range of cells containing the values to search.

• • result_vector: The range of cells containing the values to return.

Example: To find the first sales figure that is greater than or equal to 1000 in a sorted list of sales data, you could use:

=LOOKUP(1000, B2:B10, A2:A10)

This formula searches for 1000 in column B and returns the corresponding value from column A.

VLOOKUP is primarily used for vertical searches, where you want to find a value in a column based on a value in another column. Imagine you have a spreadsheet of your favorite movies, organized with columns for Title, Director, and Release Year. To find the release year of the movie “The Lord of the Rings,” you would use VLOOKUP. You’d provide VLOOKUP with the title, the range of cells containing your data, the column number where the release year is located, and whether you want an exact or approximate match.

I remember a time when our marketing team needed to find the sales figures for a specific product. We had a large spreadsheet with product names, sales quantities, and prices. Using VLOOKUP, we were able to quickly look up the sales figures for any product by simply entering its name.

Syntax:

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

• • lookup_value: The value you want to search for.

• • table_array: The range of cells containing the data you want to search.

• • col_index_num: The column number from which you want to return a value.

• • range_lookup: An optional argument that specifies whether to find an exact or approximate match.

Example: Imagine you have a spreadsheet with a list of products and their prices. To find the price of a product named “Laptop,” you would use the following formula:

=VLOOKUP("Laptop", A2:B10, 2, FALSE)

This formula searches for “Laptop” in column A (assuming the product names are in column A), returns the value from column 2 (Price), and requires an exact match.

HLOOKUP is used for horizontal searches, where you want to find a value in a row based on a value in another row.

Think of HLOOKUP as a way to search for information across a row. For example, if you have a spreadsheet with monthly sales data for different products, you could use HLOOKUP to find the sales for a specific month.

• • Syntax:

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

• • lookup_value: The value you want to search for.

• • table_array: The range of cells containing the data you want to search.

• • row_index_num: The row number from which you want to return a value.

• • range_lookup: An optional argument that specifies whether to find an exact or approximate match.

I once had to prepare a sales report that showed the total sales for each product across different months. Using HLOOKUP, I was able to easily extract the sales data for each product and calculate the totals. the formula would have looked like this:

=SUM(HLOOKUP("Product A", A2:D4, 2, FALSE):HLOOKUP("Product A", A2:D4, 4, FALSE))

Breakdown of the formula:

• • HLOOKUP("Product A", A2:D4, 2, FALSE): This part finds the sales for “Product A” in January (row 2).

• • HLOOKUP("Product A", A2:D4, 4, FALSE): This part finds the sales for “Product A” in March (row 4).

• • The whole formula sums the sales figures from January to March.

• • Don’t be bored or overwhelmed by this, you don’t have to cram that or know all that gibberish for now. The purpose of that was just to help you understand how dynamic using lookup can be. However, below is a more familiar example you can start with:

• • If you have a table with monthly sales data for different products, you could use HLOOKUP to find the sales for a specific month. For example, to find the sales for “Product A” in “January,” you would use:

• • =HLOOKUP(“January”, A2:C10, 2, FALSE)

INDEX/MATCH is a combination of functions that provides greater control over the lookup processes. Index specifies the row and column where the value is located, while MATCH finds the correct row or column based on a lookup value.

We were working on a complex financial report that required us to extract data from multiple worksheets. Using INDEX/MATCH, we were able to dynamically reference data from different sheets based on specific criteria.

INDEX/MATCH is a combination of functions that provides greater control over the lookup process.

Syntax:

=INDEX(array, row_num, [column_num])
=MATCH(lookup_value, lookup_array, [match_type])

• • array: The range of cells containing the data you want to search.

• • row_num: The row number from which you want to return a value.

• • column_num: The column number from which you want to return a value.

• • lookup_value: The value you want to search for.

• • lookup_array: The range of cells containing the values to search.

• • match_type: An optional argument that specifies the type of match (exact or approximate).

Example: To find the price of a product named “Laptop,” you could use:

=INDEX(B2:B10, MATCH("Laptop", A2:A10, 0))

This formula first uses MATCH to find the row number where “Laptop” is in column A, and then uses INDEX to return the value from column B in that row.

Do you know about the swiss army knife? It was invented dating back to the late 19th century. It all started in Switzerland when the military needed a reliable, multi-functional tool for their soldiers. The knife was initially designed to be a practical tool for soldiers in the field, equipped with essential features like a blade, screwdriver, reamer, and can opener, but over the years has been equipped with more features based on changing needs.

Such is the Xlookup. it was created to perform tasks of any lookups function. It’s a versatile function that can perform various lookup tasks, making it a valuable addition to your Excel toolkit. it can perform both vertical and horizontal lookups, making it a powerful tool for various data analysis tasks from easy to complex. XLOOKUP allows you to search for a value based on multiple criteria. For example, you could find a product based on its name and category.

Our sales team needed to identify products that were both popular and profitable. Using XLOOKUP, we were able to filter our product data based on sales volume and profit margin, allowing us to pinpoint the most successful products.

Syntax:

=XLOOKUP(lookup_value, lookup_array, return_array, [not_found], [match_mode], [search_mode])

• • lookup_value: The value you want to search for.

• • lookup_array: The range of cells containing the values to search.

• • return_array: The range of cells containing the values to return.

• • not_found: An optional argument that specifies what to return if the lookup value is not found.

• • match_mode: An optional argument that specifies the type of match (exact or approximate).

• • search_mode: An optional argument that specifies the search direction (left-to-right or top-to-bottom).

Example: To find the price of a product named “Laptop” from the category “Electronics,” you could use:

=XLOOKUP("Laptop", A2:A10, C2:C10, "Product not found", 0, 1)

This formula searches for “Laptop” in column A, returns the corresponding value from column C (Price), and specifies that an exact match is required.

Other Common Use Cases

• • Inventory Management: Need to find the price of a specific product? VLOOKUP is your go-to.

• • Sales Analysis: Want to calculate total sales for a particular month? HLOOKUP can help.

• • Customer Relationship Management (CRM): Need to find a customer’s contact information based on their name? XLOOKUP is your friend.

• • Financial Reporting: Want to extract specific financial data for analysis? INDEX/MATCH can do the trick.

Finally these are functions that best fits different case scenarios and use cases. By the time you here, you will be ready to take the next step further in you analytics journey. A cheat sheet has been provided to help you practice and make you a pro, download below. The secret is to always remember the basics. If you want to become a pro in data analysis with excel, or any other powerful tool used in the tech industry today such as powerBI, check out camanda by clicking here or reach out through an email at hello@camanda.academy.