VLOOKUP inexact match

This lesson shows how to perform a VLOOKUP inexact match (this is the less usual case as almost all VLOOKUPS found in business workbooks use exact matches).

This article is an unedited lesson from one of the 28 short focused lessons in the Advanced Functions Session of my “Expert Skills” book.

Leave me a comment if you find the lesson useful.

Last Updated: September 29, 2020

note

The HLOOKUP function works in the same way as the VLOOKUP function

In the VLOOKUP function, V is an abbreviation of Vertical. This means that you search for a matching value in a (vertical) column and return a value from any column in that row.

In the HLOOKUP function, H is an abbreviation of Horizontal. This means that you can search for a matching value in a (horizontal) row and return a value from any row in that column.

You’ll see the VLOOKUP function used far more often than HLOOKUP but you may find a scenario where it is useful.

In: Lesson 3‑22: Use a VLOOKUP function for an exact lookup (sidebar), you learned that the new XLOOKUP dynamic array function has now completely replaced VLOOKUP.

XLOOKUP also replaces HLOOKUP as it is able to lookup both horizontally and vertically.

Unless you need to share your workbooks with users of Excel 2019 and earlier, you should use XLOOKUP for all new work.

You’ll learn about dynamic arrays later, in: Session Four: Working with Dynamic Arrays.

You’ll learn about the modern XLOOKUP replacement for VLOOKUP and HLOOKUP later, in:

Lesson 4‑18: Understand XLOOKUP (part one)
and
Lesson 4‑19: Understand XLOOKUP (part two)

note

XLOOKUP is more sophisticated than VLOOKUP

In: Lesson 3‑22: Use a VLOOKUP function for an exact lookup (sidebar), you learned that the new XLOOKUP dynamic array function has now completely replaced VLOOKUP.

You should only use VLOOKUP in new work when you need to maintain compatibility with Excel 2019 and earlier versions.

XLOOKUP can perform more advanced inexact lookups than VLOOKUP:

VLOOKUP always returns an exact match or next smaller item. XLOOKUP can return either the next smaller or next larger item when an exact match is not found.

XLOOKUP does not require a range to be sorted in ascending order.
XLOOKUP supports wildcard character matches.

You’ll learn about dynamic arrays later, in: Session Four: Working with Dynamic Arrays.

Lesson 4‑18: Understand XLOOKUP (part one)
and
Lesson 4‑19: Understand XLOOKUP (part two)

Lesson 3 25: Use a VLOOKUP function for an inexact match

You will rarely encounter a VOOKUP inexact match in business workbooks.

In: Lesson 3‑22: Use a VLOOKUP function for an exact lookup, you set the fourth argument of the VLOOKUP to FALSE in order to achieve an exact lookup. Most of the time, that’s exactly what you want to do.

Sometimes you don’t want to search for an exact match but are interested in a close match. This is called an inexact lookup. Consider the following exam grades:

An exact VLOOKUP for a student with a mark of 80% would correctly return a grade of A.

An exact VLOOKUP for a student with a mark of 77% would result in an error because there is no exact value of 77% in column E.

If you ask VLOOKUP to perform an inexact lookup, it will return an exact match if one is found. If an exact match is not found, it will return the largest value that is less than the lookup value.

For VLOOKUP to work with inexact matches, it is vital that the lookup column is sorted in ascending order (from the lowest to the highest value).

In the above example, an inexact search for 68% would find row 5 (a C grade) because 60% is the largest value that is less than 68%.

1. Open the sample file Grades-1 in Excel.

Excel shows a standard warning for any file obtained from the Internet.

The file is quite safe to open but if Excel’s warning worries you you can download a digitally signed sample file set that will not display this warning more…

Click Enable Editing.

2. Convert cells E3:F8 into a table named: Grade.

This was covered in: Lesson 1 13: Convert a range into a table and add a total row and Lesson 1 19: Name a table and create an automatic structured table reference.

When working with the VLOOKUP function it is best practice to use a table for the Table_array argument.

This will make the data dynamic. In other words, the VLOOKUP function will still work correctly if you add and remove grades from the Grade table.

3. Add an inexact VLOOKUP to cell C4 to return the grade that corresponds to the percentage mark in cell B4.

You learned how to add an exact VLOOKUP in Lesson 3‑22: Use a VLOOKUP function for an exact lookup. An inexact lookup is done in exactly the same way, except that the Range_lookup argument is set to TRUE (or omitted, as the default is TRUE).

This time, the correct arguments are therefore:

4. AutoFill cell C4 to cells C5:C17.

The correct grades are now shown for each student.

5. Save your work as Grades-2.

Here’s the sample file at the end of the session. If you ran into any problems with your VLOOKUP inexact match you can study the finished workbook to identify the problem.