Essential to Expert Skills progression

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

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.

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:

vlookup inexact match sample file grab

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:

vlookup inexact match function arguments dialog

4. AutoFill cell C4 to cells C5:C17.

The correct grades are now shown for each student.

vlookup inexact match finished workbook

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.

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)

 

Learn Excel 365 Expert Skills with The Smart Method (fourth edition) - Front cover

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

Related Articles

VLOOKUP exact match

Simple VLOOKUP tutorial with sample file that will teach you everything there is to know when creating a VLOOKUP function with an exact match.

x-lookup-tutorial-part-one

Excel XLOOKUP function (part one)

The new Excel XLOOKUP function was introduced in the July 2020 Excel 365 semi-annual update.  It isn’t available in older versions (Excel 2019 and earlier). 

VLOOKUP using text strings

VLOOKUP using text strings

How to use VLOOKUP to search for a piece of text and return a matching value. This article also explains what VLOOKUP is and when to use it.

Share this article

Share on facebook
Share on twitter
Share on linkedin
Share on stumbleupon
Share on email

Leave a Reply

Your email address will not be published.

17 − eleven =

Cross-lesson hyperlinks

In order to use all cross-lesson hyperlinks you will need to download the full e-book. The Expert Skills e-book contains 253 short lessons that will teach you everything there is to know about Excel.

When you download the e-book you’ll be able to access every lesson,  online or offline, on all of your devices, using Amazon’s free advanced e-book reader