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

## Lessons in Session 3

 Session Three: Advanced Functions Lesson 3‑1: Understand precedence rules and use the Evaluate feature Lesson 3‑2: Use common functions with Formula AutoComplete Lesson 3‑3: Use the Insert Function dialog and the PMT function Lesson 3‑4: Use the PV and FV functions to value investments Lesson 3‑5: Use the IF logic function Lesson 3‑6: Use the SUMIF and COUNTIF functions to create conditional totals Lesson 3‑7: Understand date serial numbers Lesson 3‑8: Understand common date functions Lesson 3‑9: Use the DATEDIF function Lesson 3‑10: Use date offsets to manage projects using the scheduling equation Lesson 3‑11: Use the DATE function to offset days, months and years Lesson 3‑12: Enter time values and perform basic time calculations Lesson 3‑13: Perform time calculations that span midnight Lesson 3‑14: Understand common time functions and convert date serial numbers to decimal values Lesson 3‑15: Use the TIME function to offset hours, minutes and seconds Lesson 3‑16: Use the AND and OR functions to construct complex Boolean criteria Lesson 3‑17: Understand calculation options (manual and automatic) Lesson 3‑18: Concatenate strings using the concatenation operator (&) Lesson 3‑19: Use the TEXT function to format numerical values as strings Custom format strings recap Lesson 3‑20: Extract text from fixed width strings using the LEFT, RIGHT and MID functions Lesson 3‑21: Extract text from delimited strings using the FIND and LEN functions Lesson 3‑22: Use a VLOOKUP function for an exact lookup Lesson 3‑23: Use the SWITCH function Lesson 3‑24: Use an IFERROR function to suppress error messages Lesson 3‑25: Use a VLOOKUP function for an inexact lookup Lesson 3‑26: Use a MATCH function for an exact lookup Lesson 3‑27: Use the INDEX function Lesson 3‑28: Use the IFS function Session 3: Exercise Session 3: Exercise Answers