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.

- Excel 365

- Excel 2019

- Excel 2016

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

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 |

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

### Excel XLOOKUP function (part two)

This is the second of a two-part series of XLOOKUP lessons. If you haven’t already done so I recommend that you begin with 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).

### Using VLOOKUP, if Column 1 is blank, get value from Column 2

This article shows how to create an Excel VLOOKUP formula that extracts data from a different column if the first column it searches is blank.

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