Mastering VLOOKUP and HLOOKUP in Excel

 

Introduction:

When it comes to working with spreadsheets and analyzing data, VLOOKUP and HLOOKUP are two powerful features that can greatly simplify your tasks. You can use these functions to search for specific values ​​in a table and retrieve the corresponding data. In this blog post, we'll take a closer look at VLOOKUP (vertical search) and HLOOKUP (horizontal search) to give you a thorough understanding of their capabilities and practices.

1. VLOOKUP: Vertical data acquisition:

VLOOKUP is used to look up values ​​in the leftmost column of a table and retrieve data from a specific column. The basic syntax is:

=VLOOKUP(lookup value, table array, column index number, [lookup range])

- 'reference value': The value you want to retrieve.

- 'Table Array': The range of cells containing the data to search.

- 'col index num': Column number to retrieve results from.

- 'range lookup' (optional): A logical value that determines whether to perform an exact match or an approximate match.

Example 1:

Find product information

Suppose you have a table containing a product inventory list, with column A containing the product names and column B containing the corresponding prices. You receive a new order and need to quickly determine the price of a particular product. VLOOKUP can help. Here is an example expression: 

=VLOOKUP("Product X", A2:B10, 2, FALSE)

In this case, "Product X" is the value you want to search for. Area A2:B10 contains the product name and price, and we want to get the price (indicated by the second column, '2'). A logical value FALSE guarantees an exact match.

Example 2:

Classify sales data

Imagine you have a record of sales transactions and want to categorize each transaction based on the product sold. I have another table that maps products to appropriate categories. VLOOKUP allows you to automatically assign categories to your sales data. Here is an example expression:

=VLOOKUP(A2, Productstable, 2, FALSE)

In this case A2 refers to the product name in the sales record. ProductsTable represents a range of cells where product category mappings exist. I would like to get the category (second column) based on the exact match.

Example 3:

Handling errors with VLOOKUP

It is important to consider possible errors when using VLOOKUP. VLOOKUP returns a #N/A error if the lookup value is not found in the table. To resolve this issue, use the IFERROR function to display a custom message or perform an alternative action. An example is shown below.

=IFERROR(VLOOKUP("Product Y", Productstable, 2, FALSE), "not found")

In this case, if "product Y" was not found in “Productstable”, the IFERROR function displays the message "Not Found" instead of an error.

  

 

2. HLookup : Horizontal data acquisition:

HLOOKUP works like VLOOKUP, but horizontally. Look up values ​​in the top row of the table and get the data from the specified row. The basic syntax is:

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

- 'lookup value':The value you want to retrieve.

- 'Table Array':The range of cells containing the data to search.

- 'row index number': Row number to retrieve results from.

- 'range lookup' (optional): A logical value that determines whether to do an exact match or an approximate match.

Example 4:

Sales analysis by region

Imagine a sales record with the region listed in the first row and the corresponding sales figures in the subsequent rows. I would like to analyze sales for a specific region. HLOOKUP helps to get sales figures based on locality name. Here is an example expression: 

=HLOOKUP("West", A1:E2, 2, FALSE)

In this case, "West" is the value to look for in the top row. Area A1:E2 contains data and I want to get the value from the second row (denoted by "2"). A logical value FALSE guarantees an exact match.

Example 5:

Sales forecast by month

Imagine you have a sales forecast table with the months listed in the first row and the corresponding sales forecasts in subsequent rows. Suppose you want to forecast sales for a particular month. HLOOKUP allows you to get sales by month name. Here is an example expression: 

=HLOOKUP("May", A1:E4, 3, FALSE)

In this case, the value to search for in the top row is "May". Area A1:E4 contains data and I want to get the value from the third row (denoted by "3"). A logical value FALSE guarantees an exact match.

                                                             Now Lets combine both the formulas.

Example 6:

Cross-references to product data

Imagine you have two tables.

One table has product names in column A and corresponding prices in column B, and another table has product names in the first row and corresponding quantities in subsequent rows. Suppose we want to get the price and quantity of  product B. Combining VLOOKUP and HLOOKUP makes it easy to cross-reference data. Here is an example expression:

=VLOOKUP("Product B", A1:B5, 2, FALSE) * HLOOKUP("Product B", F1:J2, 2, FALSE)

In this example, "Product B" is the value to look for in both tables. The VLOOKUP function returns the first table (range A1:B5), the HLOOKUP function is the second table (range F1:J2). By multiplying the price and quantity, we can calculate the total price of the product.

Conclusion:

Both VLOOKUP and HLOOKUP are powerful function that allows you to retrieve data from tables, enabling efficient data analysis and decision-making. By mastering both formulas and applying it to real-life scenarios, you can unlock valuable insights from your datasets. Experiment with VLOOKUP and HLOOKUP in your own spreadsheets, explore its additional features, and harness its potential to enhance your data analysis capabilities.

                                           Click Here to Download latest MS Office