How to Use VLOOKUP to Find the Closest Match: Expert Guide
Are you looking for an easy way to find the closest match for a value in Excel? The VLOOKUP function is a powerful tool that allows you to search for a specific value in a table and return the corresponding data from another column. In this article, we’ll show you step-by-step how to use VLOOKUP to find the closest match, even if an exact match doesn’t exist. By the end, you’ll be a pro at using this essential Excel function to quickly locate nearly matching data.
Understanding the VLOOKUP Function
Before we learn about finding the closest match, let’s review the basics of the VLOOKUP function. VLOOKUP searches for a value in the leftmost column of a table and returns a corresponding value from a specified column. The syntax is:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
- lookup_value: The value to search for in the first column of the table
- table_array: The table of data to search within
- col_index_num: The column number in the table that contains the return value
- [range_lookup]: Optional. Enter FALSE for an exact match or TRUE (or omit) to find the closest match below the lookup value.
Here’s a simple example:
Employee | Department | Salary |
---|---|---|
John | Sales | $50,000 |
Alice | Marketing | $60,000 |
Bob | IT | $75,000 |
To look up Alice’s salary, use:
=VLOOKUP(“Alice”, A2:C4, 3, FALSE)
This searches for “Alice” in the first column, and returns the value from the 3rd column (Salary) in the same row. The formula result is $60,000.
Finding the Closest Match with VLOOKUP
What if you need to find a close match, but not an exact one? For example, searching for a number within a range, or the best fit based on multiple criteria.
The key is using TRUE (or omitting) the optional 4th argument, [range_lookup]. This makes VLOOKUP work in approximate match mode. It will find the largest value less than or equal to the lookup value.
Example 1: Finding a Number Within a Range
Imagine you have a pricing table based on quantity purchased:
Minimum Qty | Price |
---|---|
1 | $10 |
10 | $9 |
25 | $8 |
50 | $7 |
To get the price for a quantity entered in cell A8, use:
=VLOOKUP(A8, A2:B5, 2, TRUE)
- If A8 contains 1-9, the formula returns $10
- For 10-24, it returns $9
- For 25-49, it returns $8
- For 50 or above, it returns $7
VLOOKUP finds the closest Minimum Qty less than or equal to the lookup value. This allows it to work with ranges of values.
Here’s another example that demonstrates how VLOOKUP handles numbers outside the defined ranges:
Test Score | Grade |
---|---|
0 | F |
60 | D |
70 | C |
80 | B |
90 | A |
With this formula:
=VLOOKUP(B2, A2:B6, 2, TRUE)
- A score of 0-59 returns F
- 60-69 returns D
- 70-79 returns C
- 80-89 returns B
- 90 or above returns A
Because VLOOKUP finds the largest value less than or equal to the lookup value, it correctly assigns the grade even for scores between the specified boundaries.
Example 2: Matching Names with Spelling Differences
You can also use VLOOKUP to find close matches for text. This is helpful when looking up names that may have slight spelling differences.
Suppose you have a master list of employee names:
Full Name |
---|
Jon Smith |
Bob Jones |
Sue Taylor |
And a second table that needs to reference employee data:
Name Entered | Employee ID |
---|---|
John Smith | 1001 |
Bob Jones | 1002 |
Sue Tailor | 1003 |
To get the correct Employee ID even with variations like “Jon” vs “John” or “Taylor” vs “Tailor”, use this formula:
=VLOOKUP(““&A2&”“, B2:C4, 2, FALSE)
The key parts are:
- Concatenate “*” before and after the lookup value. This wildcard character matches any text, allowing partial matches.
- Use FALSE to prevent matching names like “Jon Smith” and “John Smithson”
The formula searches the Full Name column for a cell containing the [Name Entered], ignoring differences in beginning or end. It returns the corresponding Employee ID.
Let’s consider another scenario where using wildcards with VLOOKUP is beneficial. Say you have a parts catalog like this:
Part Number | Description | Price |
---|---|---|
ABC-1234 | Widget, Type 1 | $10.99 |
ABC-1235 | Widget, Type 2 | $12.99 |
XYZ-5678 | Gadget, Small | $5.99 |
XYZ-5679 | Gadget, Large | $8.99 |
QRS-3333 | Doohickey, Standard | $7.50 |
If you have a part number in A10 like “ABC-1234-001”, you can find the matching base part with:
=VLOOKUP(A10&”*”, A2:C6, 2, FALSE)
This looks for a part number in column A that starts with the value in A10, ignoring any trailing characters. It returns the corresponding Description.
The wildcard match allows you to find the right part even if the numbering scheme includes additional details like revision numbers or lot codes.
Tips for Using VLOOKUP to Find Close Matches
Here are a few more tips to help you effectively use VLOOKUP for finding nearest matches:
Sort Your Lookup Table
For VLOOKUP to work correctly in approximate match mode, the lookup column (column A in the examples) must be sorted in ascending order. Otherwise it may return incorrect results.
If your data isn’t sorted, you can quickly sort it by selecting any cell in the table and clicking Data > Sort from the ribbon. Choose to sort by the lookup column, in A to Z order.
Handle Missing Matches with IFERROR
If no match is found, VLOOKUP normally returns a #N/A error. To make your formulas more user-friendly, catch this error with the IFERROR function:
=IFERROR(VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]), “No Match Found”)
Now if VLOOKUP can’t find a suitable match, the formula will instead return the text “No Match Found” (or whatever you specify).
Use INDEX and MATCH for Left Lookups
One limitation of VLOOKUP is that it always searches the leftmost column of the table. To match against columns further to the right, you can combine the INDEX and MATCH functions instead:
=INDEX(table_array, MATCH(lookup_value, lookup_array, match_type), return_column)
This searches lookup_array for a value that matches lookup_value, based on match_type (0 for exact match, 1 for approximate). It returns a corresponding value from the return_column in the same row as the match.
For example, with this table:
Employee ID | Name | Department |
---|---|---|
1001 | John Smith | Sales |
1002 | Jane Doe | Marketing |
1003 | Bob Jones | IT |
To look up the department for employee 1002, use:
=INDEX(A1:C4, MATCH(1002, A2:A4, 0), 3)
The MATCH function locates employee 1002 in column A, and INDEX returns the value from the 3rd column (Department) in the same row. The result is “Marketing”.
Final Thoughts
The VLOOKUP function is an incredibly versatile tool for finding data matches in Excel, even when an exact match doesn’t exist. By setting the 4th argument to TRUE or omitting it, you unlock the power to find the closest match based on your lookup value.
Whether you need to locate a number within a range, find text with minor spelling differences, or match based on a portion of a value, VLOOKUP can handle the task. The key is understanding how to use wildcards, sort your data correctly, and interpret the results.
FAQs
How do you use VLOOKUP to find the closest match?
What should you do if VLOOKUP returns a #N/A error?
Can VLOOKUP find closest matches for text values?
What is the correct way to sort data for VLOOKUP approximate match mode?
How can you perform a VLOOKUP-style match on columns other than the leftmost one?
Vaishvi Desai is the founder of Excelsamurai and a passionate Excel enthusiast with years of experience in data analysis and spreadsheet management. With a mission to help others harness the power of Excel, Vaishvi shares her expertise through concise, easy-to-follow tutorials on shortcuts, formulas, Pivot Tables, and VBA.