How to use VLOOKUP exact and approximate match in Excel?
VLOOKUP is a commonly used function in Excel for searching specific information within large datasets. By referencing a value in the left-most column of your table, VLOOKUP retrieves related information from other columns in the same row. Despite its popularity, some users encounter difficulties either due to incorrect parameter settings, error handling, or alternative lookup needs. This comprehensive guide explains both exact and approximate match usage for VLOOKUP, discusses when each is appropriate, explores built-in and alternative solutions, and introduces troubleshooting tips for a more productive lookup experience.
Use vlookup function to get the exact matches in Excel
Vlookup to get the exact matches with a handy feature
Use vlookup function to get the approximate matches in Excel
Use INDEX and MATCH functions for flexible lookups (alternative to VLOOKUP)
VBA Code to automate lookup for exact and approximate matches
Use vlookup function to get the exact matches in Excel
Before applying VLOOKUP, it’s essential to understand its syntax and how each parameter works for your data.
Here’s the standard VLOOKUP function in Excel:
- lookup_value: The value you want to search for in the first column of your chosen table.
- table_array: The cell range containing your data (e.g., A1:D10), or a named range.
- col_index_num: The column number in the data range from which you want to retrieve the result.
- range_lookup: Optional parameter. Use FALSE for exact match; TRUE for approximate match (or omit, default is TRUE).
For example, suppose you have a list of persons’ information in the cell range A2:D12, as shown below:
If you need to retrieve the names matching IDs listed in column F, enter the following formula in a blank cell where you want the result (for instance, G2):
Press "Enter", then drag the fill handle down to copy the formula to other rows, so each relevant ID returns the associated name. The outcome looks like this:
Explanation & tips:
1. F2: Cell with the lookup value (ID to find).
2. A2:D12: Data range, covering the table including both IDs and Names.
3. 2: Column index number, pointing to the second column (Names) in your chosen range.
4. FALSE: Ensures the function finds only exact matches for the ID.
5. If the exact value is missing in the range, Excel displays the #N/A error, meaning the lookup could not find a match. Double-check data cleanliness and spelling.
6. Avoid accidental reference errors—ensure your ranges are locked (with $ symbols) if you plan to copy the formula.
7. If your lookup table might expand or contract in size, consider using named ranges for improved formula stability.
Vlookup to get the exact matches with a handy feature
Users who want quicker, more interactive lookup capabilities in Excel can take advantage of Kutools for Excel. Its Look for a value in list feature simplifies lookup operations, especially for those not comfortable with typing formulas or who need more customizable guidance.
Once Kutools for Excel is installed, follow these practical steps:
1. Select the cell where you want the lookup result to appear.
2. Navigate through Kutools > Formula Helper > Formula Helper, like this:
3. In the Formulas Helper dialog box:
- From Formula Type, choose the Lookup category.
- Select Look for a value in list in the formula list.
- Fill in the argument input boxes:
- Click the first
to choose your table array. - Click the second for the lookup value (e.g., the cell with ID or Name).
- Click the third to choose the column you wish to extract data from.
to choose your table array.
4. Hit OK. The first matched value will be displayed instantly. Use the fill handle to copy the formula down as needed.
Tips for use:
- This method is ideal for users who prefer clicking to configure lookups rather than working through formulas by hand.
- If the item isn’t found, Kutools returns #N/A, just like the standard VLOOKUP—verify your input values and data formatting.
- Remember to keep Kutools updated for access to more features and improvements.
Download and free trial of Kutools for Excel Now!
Use vlookup function to get the approximate matches in Excel
For situations where the lookup value does not exist in your list, you may need to find the nearest or next-largest match instead. This scenario is common in pricing tables, grade boundaries, or commission calculations. VLOOKUP supports approximate matching via the TRUE setting in its final parameter.
Suppose you have this data, with the desired quantity (like 58) not directly present in the Quantity column, yet you need to find its closest corresponding unit price:
Input this formula into a blank cell, such as C2:
Press Enter, then drag the fill handle down to fill other rows. Excel will return approximate matches based on your lookup values, as shown:
Important reminders:
1. D2 = Lookup value (quantity you wish to match).
2. A2:B10 = Table range containing quantities and prices.
3. 2 = Second column (unit price) for return value.
4. TRUE = Activates the approximate match. VLOOKUP finds the largest value less than or equal to your lookup value.
5. Sorting is essential: Ensure the first column (Quantity) is sorted in ascending order; otherwise, results may be unpredictable or incorrect.
6. For complex thresholds, such as commission structures, this approach helps rapidly identify applicable rates based on ranges.
7. When using approximate match for granular values (like grades, ranges, or sliding scales), always verify table structure and sorting before applying or sharing formulas.
Use INDEX and MATCH functions for flexible lookups (alternative to VLOOKUP)
In many cases, VLOOKUP isn’t suitable—especially when your lookup data does not reside in the first column or you wish to search horizontally or flexibly in datasets. INDEX and MATCH functions offer a robust alternative for both exact and approximate matches: you’re not constrained by column order and can match in any direction.
This solution is widely used in scenarios such as employee record lookups, where ID or Name might not be in the left-most column, or for comparing values in non-adjacent data ranges.
Advantages: Works for vertical and horizontal lookups, does not require sorting, and allows for more complex matching conditions.
Disadvantages: Slightly more complex to set up compared to VLOOKUP, requires understanding nested functions.
1. To perform an exact match lookup (for example, finding an employee’s department based on their ID), enter the following formula in a blank cell such as G2:
=INDEX($C$2:$C$12,MATCH(F2,$A$2:$A$12,0))Here, F2 is your lookup value (the employee ID), $A$2:$A$12 is the range to search for the ID, and $C$2:$C$12 is the column containing Department names. The 0 in MATCH means “exact match.”
Press Enter, then drag down to apply the formula to other rows. If the ID isn’t found, you’ll get an #N/A error. Consider using IFERROR or data validation for even smoother experience.
2. For approximate match lookups, use the following formula (for cases such as finding a grade boundary):
=INDEX($B$2:$B$10,MATCH(D2,$A$2:$A$10,1))Here, D2 is the lookup value, $A$2:$A$10 is the sorted reference range (ascending order), and $B$2:$B$10 contains the returned values. The 1 in MATCH enables approximate matching, returning the largest value less than or equal to your lookup.
Remember: If you drag formulas, keep absolute references for table ranges to maintain correct lookups. Use IFERROR for user-friendly blank or custom messages instead of errors.
Troubleshooting tips: If your formula returns errors, verify sorting for approximate matches, check cell ranges, and ensure lookup values are correctly formatted (e.g., text versus numbers).
VBA Code to automate lookup for exact and approximate matches
For advanced users or complex repetitive lookup tasks, a VBA macro can streamline searching for exact or approximate matches. This is especially beneficial when performing multiple lookups, exporting results to new sheets, or automating data processes not supported by standard Excel formulas.
This method is ideal when your lookup range or criteria changes frequently, or when integrating search within larger automated workflows.
1. Begin by opening the VBA editor. Go to Developer Tools > Visual Basic. In the window that appears, click Insert > Module.
Paste the following VBA code into the module:
Sub KutoolsVLookupMacro()
Dim lookupValue As Variant
Dim lookupRange As Range
Dim colNum As Integer
Dim rangeType As String
Dim result As Variant
Dim xTitleId As String
xTitleId = "KutoolsforExcel"
On Error Resume Next
Set lookupRange = Application.InputBox("Select lookup table range", xTitleId, Type:=8)
lookupValue = Application.InputBox("Enter value to look up", xTitleId, Type:=2)
colNum = Application.InputBox("Enter return column number from the table", xTitleId, Type:=1)
rangeType = Application.InputBox("Exact match (FALSE) or Approximate match (TRUE)?", xTitleId, "FALSE", Type:=2)
If rangeType = "TRUE" Or rangeType = "true" Then
result = Application.WorksheetFunction.VLookup(lookupValue, lookupRange, colNum, True)
Else
result = Application.WorksheetFunction.VLookup(lookupValue, lookupRange, colNum, False)
End If
If IsError(result) Then
MsgBox "Lookup failed – no matching value found.", vbExclamation, xTitleId
Else
MsgBox "Found value: " & result, vbInformation, xTitleId
End If
End Sub2. To run, click the 
button. Follow the prompts in the dialog boxes to select your table range, enter a value, input the return column number, and specify whether you want an exact or approximate match (type FALSE for exact, TRUE for approximate).
Once the process finishes, you will see either the matched value or a notification if no match was found. This approach reduces manual errors and is ideal for repeated tasks. If unexpected results occur, check for correct range selection, column numbering, and value type consistency (e.g., number vs text).
Tips: Always save your work before running or editing macros. For bulk lookups, consider further customizing the VBA script to loop through a list of values or output results to another worksheet.
More relative VLOOKUP articles:
- Vlookup And Concatenate Multiple Corresponding Values
- As we all known, the Vlookup function in Excel can help us to lookup a value and return the corresponding data in another column, but in general, it can only get the first relative value if there are multiple matching data. In this article, I will talk about how to vlookup and concatenate multiple corresponding values in only one cell or a vertical list.
- Vlookup And Return The Last Matching Value
- If you have a list of items which are repeated many times, and now, you just want to know the last matching value with your specified data. For example, I have the following data range, there are duplicate product names in column A but different names in column C, and I want to return the last matching item Cheryl of the product Apple.
- Vlookup Values Across Multiple Worksheets
- In excel, we can easily apply the vlookup function to return the matching values in a single table of a worksheet. But, have you ever considered that how to vlookup value across multiple worksheet? Supposing I have the following three worksheets with range of data, and now, I want to get part of the corresponding values based on the criteria from these three worksheets.
- Vlookup And Return Whole / Entire Row Of A Matched Value
- Normally, you can vlookup and return a matching value from a range of data by using the Vlookup function, but, have you ever tried to find and return the whole row of data based on specific criteria.
- Vlookup Across Multiple Sheets And Sum Results
- Supposing, I have four worksheets which have the same formatting, and now, I want to find the TV set in the Product column of each sheet, and get the total number of order across those sheets as following screenshot shown. How could I solve this problem with an easy and quick method in Excel?
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in