 Publishers of the world’s most comprehensive and up-to-date Excel tutorials ## Excel tutorials

The only Excel books you will ever need

• The only constantly updated Excel 365 titles more…
• Thoroughly covers Power Pivot and Power Query more…
• Covers business intelligence and OLAP features more…
• Simple, easy, understandable, 2-page lessons more…
• Available as both printed books and e-Books more…
• Used by schools, colleges and universities  more…
• Available for 365, 2021, 2019, 2016 and Mac versions more…

# 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).

Leave me a comment if you find the lesson useful.

## 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. 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.

I hope that you enjoyed the lesson and will now be completely confident creating any VLOOKUP inexact match.

• 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) This lesson is excerpted from the above book.

This is the only up-to-date Excel book currently published and includes an entire session devoted to the new Dynamic Arrays features.

It is also the only book that will teach you absolutely every Excel skill including Power Pivot, OLAP and DAX.

## Some of the things you will learn              ## Which tutorial do you need?   ### Which Excel Essential Skills version do you need to learn?

#### Excel 2016 for Mac 