vegibit

Approximate and Exact Matches With Excel VLOOKUP and HLOOKUP

,
Click to share! ⬇️

Excel’s VLOOKUP and HLOOKUP functions are used to search for and retrieve data from a table or range of cells. VLOOKUP, or “vertical lookup,” searches for data in the first column of a table and returns a value from a specified column in the same row. HLOOKUP, or “horizontal lookup,” searches for data in the first row of a table and returns a value from a specified row in the same column.

Both functions have the following syntax:

VLOOKUP(value, table, column, [approximate match]) 

HLOOKUP(value, table, row, [approximate match])

The “value” argument is the value you want to search for. The “table” argument is the range of cells containing the data you want to search. The “column” or “row” argument is the number of columns or rows from which the function should return a value. The optional “approximate match” argument is a Boolean value that specifies whether an approximate or exact match should be returned. An approximate match is returned if this argument is set to TRUE or omitted. If set to FALSE, an exact match is returned.

It’s important to note that when using the VLOOKUP and HLOOKUP functions, the data table must be sorted in ascending order on the first column for VLOOKUP and the first row for HLOOKUP for the approximate match to work.

Both functions are widely used to find the data from large datasets, where data is organized in tabular format and can save a lot of time and manual work of searching values from a large dataset.

Using VLOOKUP for Exact Matches

When using the VLOOKUP function for exact matches, the fourth argument, “approximate match,” should be set to FALSE. This tells Excel only to return a result if an exact match for the specified value is found in the first column of the table.

Here’s an example:

Let’s say you have a table of data with three columns: “Product ID,” “Product Name,” and “Price.” The table is sorted in ascending order by “Product ID.” If you want to look up the price of a product with the ID of 123, you will use the following formula:

=VLOOKUP(123, A2:C8, 3, FALSE)

In this example, the value you’re searching for is 123, the table is A2:C8, the column you want to return a value from is 3 (the “Price” column), and the “approximate match” argument is set to FALSE.

If an exact match is found for the value 123 in the first column of the table, the function will return the corresponding price from the third column. The function will return the #N/A error if an exact match is not found.

It’s important to note that if the table is not sorted as it should be or there is a mismatch in the value you are looking up, you will get an error or unexpected result.

Using HLOOKUP for Exact Matches

When using the HLOOKUP function for exact matches, the fourth argument, “approximate match,” should be set to FALSE. This tells Excel only to return a result if an exact match for the specified value is found in the first row of the table.

Here’s an example:

Let’s say you have a table of data with three rows: “Product Name”,”Price”,” Product ID” and “Quantity.” The table is sorted in ascending order by “Product Name.”

If you want to look up the Quantity of a product with the name of “apple”, you would use the following formula:

=HLOOKUP("apple",A2:D4,4,FALSE)

In this example, the value you’re searching for is “apple”, the table is A2:D4, the row you want to return a value from is 4 (the “Quantity” row), and the “approximate match” argument is set to FALSE.

If an exact match is found for the value “apple” in the first row of the table, the function will return the corresponding Quantity from the Fourth row. The function will return the #N/A error if an exact match is not found.

It’s important to keep in mind that the HLOOKUP function searches for the value in the first row of the table, so it’s important to have the values that you want to search for in the first row so that you get accurate results.

Using VLOOKUP for Approximate Matches

When using the VLOOKUP function for approximate matches, the fourth argument, “approximate match,” should be set to TRUE or omitted. This tells Excel to return a result based on an approximate match for the specified value in the first column of the table.

Here’s an example:

Let’s say you have a table of data with three columns: “Grade”,”Low Score”,”High Score” and “Letter Grade” .The table is sorted in ascending order by “Low Score”

If you want to look up the letter grade of a student with a score of 82, you will use the following formula:

=VLOOKUP(82,A2:D8,4,TRUE)

In this example, the value you’re searching for is 82, the table is A2:D8, the column you want to return a value from is 4 (the “Letter Grade” column), and the “approximate match” argument is set to TRUE.

Since the table is sorted by “Low Score” in ascending order, Excel will find the nearest value in the first column that is less than or equal to 82 and return the corresponding letter grade from the fourth column. If the value 82 is not found, it will check for the next closest match. It’s important to note that if the table is not sorted or there is no match for the value you’re looking up, this function may return unexpected results or errors.

Approximate match is useful when you have a list of data you want to match and you have fuzzy match criteria in mind.

Using HLOOKUP for Approximate Matches

When using the HLOOKUP function for approximate matches, the fourth argument, “approximate match,” should be set to TRUE or omitted. This tells Excel to return a result based on an approximate match for the specified value in the first row of the table.

Here’s an example:

Let’s say you have a table of data with three rows: “Salesperson”,”Jan”,”Feb” and “Mar” .The table is sorted in ascending order by “Jan” Sales

If you want to look up the March sales of the salesperson with the name of “John”, you would use the following formula:

=HLOOKUP("John",A2:D4,4,TRUE)

In this example, the value you’re searching for is “John”, the table is A2:D4, the row you want to return a value from is 4 (the “Mar” row), and the “approximate match” argument is set to TRUE.

Since “Jan” Sales sort the table in ascending order, Excel will find the nearest value in the first row that is less than or equal to the sales of John in Jan and return the corresponding sales in March from the fourth row.

It’s important to note that when using HLOOKUP with approximate match, the table must be sorted in ascending order on the first row and the value you’re searching for should be in the first row. If not, this function may return unexpected results or errors.

Approximate match with HLOOKUP is useful when you want to look up values that match a certain criteria, but the exact value is not available.

Common Pitfalls and Troubleshooting Tips for VLOOKUP and HLOOKUP

There are a few common pitfalls and troubleshooting tips to keep in mind when using the VLOOKUP and HLOOKUP functions in Excel:

  1. Make sure the table is sorted in ascending order on the first column for VLOOKUP and the first row for HLOOKUP. This is especially important when using the functions for approximate matches.
  2. Pay attention to the column or row number argument in the function. Ensure you’re returning the correct column or row, as specified in the function.
  3. Check the range of cells specified in the “table” argument. Ensure it includes all the necessary data and that there are no extra rows or columns.
  4. The value you’re looking for in the table should match the value’s format, the use of the correct text or number format, and the case sensitivity. If it’s not matching, you might not get the correct result.
  5. If you’re using a named range for the table argument, make sure that the named range is up-to-date and includes all the necessary data.
  6. Be careful when using the approximate match argument. If you set it to TRUE, make sure that the table is sorted in ascending order on the first column for VLOOKUP and the first row for HLOOKUP. Otherwise, you will get unexpected results.
  7. Keep an eye out for the #N/A error, which indicates that a match for the specified value was not found in the table. Double-check your function to ensure that the value and table range is correct.
  8. Be aware that if you have hidden rows or columns in your table, it can affect the results of VLOOKUP and HLOOKUP, so be sure to include all the data in your table.
Click to share! ⬇️

How To Use Excel XMATCH Function Locate Data With the Excel MATCH Function Find Data by Location With the Excel INDEX Function Use the Excel MATCH and INDEX Functions Together Excel XLOOKUP Function for Exact Matches Find Table-Like Data Within a Function Using Excel CHOOSE