Skip to main content

Easily Vlookup with multiple criteria in Excel

Author: Xiaoyang Last Modified: 2023-02-27

Kutools for Excel

Boosts Excel With 300+
Powerful Features

Normally, you can apply the Vlookup function to lookup the corresponding data in a list based on a specific criterion, but, sometimes, you may need to vlookup the relative value based on multiple criteria as below screenshot shown. If you have Kutools for Excel, with its Multi-condition Lookup feature, you can lookup the matched values in different columns based on multiple criteria with only several clicks in Excel.

Vlookup matching values based on multiple criteria in Excel

Vlookup matching values based on multiple criteria in Excel

To lookup the corresponding values from multiple different columns, please do with the following steps:

1. Click Kutools > Super LOOKUP > Multi-condition Lookup, see screenshot:

2. In the Multi-condition Lookup dialog box, please do the following operations:

(1) In the Output Range field, select the range where you want to output the matching results;
(2) In the Lookup Values field, specify the lookup value range that you want to vlookup values based on;
(3) In the Key columns field, select the corresponding key columns that contain the lookup values one by one by holding the Ctrl key;
(4) In the Return column field, select the column which contain the returned values you need.
(5) Click the OK button.

A. In the Key columns field, the number of columns you selected must be equal to the number of columns you selected in the Lookup values field, and the order of each selected column in the Key column field must correspond one to one with the criteria columns in Lookup values field.
B. If the key columns are contiguous and match the order of the Lookup values, just select the entire range of them.

Then all the matched values based on the multiple criteria have been extracted at once, see screenshot:

Note: By default, a #N/A error value will be displayed if the specific value you lookup doesn't exist. See screenshot:

To replace this #N/A error value with another text value, you need to click the Options button to open Options dialog box, check the Replace #N/A error value with a specified value option, and then type the text you need, if you want to display #N/A as empty, just leave blank in the textbox.

You can see the #N/A error is replaced with the specified text as shown in the screenshot below.

Productivity Tools Recommended
The following tools can greatly save your time and money, which one is right for you?
Office Tab: Using handy tabs in your Office, as the way of Chrome, Firefox and New Internet Explorer.
Kutools for Excel: More than 300 Advanced Functions for Excel 2021, 2019, 2016, 2013, 2010, 2007 and Office 365.

Kutools for Excel

The functionality described above is just one of 300 powerful functions of Kutools for Excel.

Designed for Excel(Office) 2021, 2019, 2016, 2013, 2010, 2007 and Office 365. Free download and use for 30 days.

Screen shot of Kutools for Excel

btn read more      btn download     btn purchase

Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
Rate this post:
0   Characters
Suggested Locations