Skip to main content

Easily Vlookup with multiple criteria in Excel

Author: Xiaoyang Last Modified: 2024-12-06

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.

shot-vlookup-multiple-criteria-

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:

shot-vlookup-multiple-criteria-2

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.

shot-vlookup-multiple-criteria-3

Note:
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:

shot-vlookup-multiple-criteria-result

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

shot-vlookup-multiple-criteria-4

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.

shot-vlookup-multiple-criteria-5

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

shot-vlookup-multiple-criteria-6

Productivity Tools Recommended

Office Tab: Use handy tabs in Microsoft Office, just like Chrome, Firefox, and the new Edge browser. Easily switch between documents with tabs ā€” no more cluttered windows. Know more...

Kutools for Outlook: Kutools for Outlook offers 100+ powerful features for Microsoft Outlook 2010ā€“2024 (and later versions), as well as Microsoft 365, helping you simplify email management and boost productivity. Know more...

Kutools for Excel

Kutools for Excel offers 300+ advanced features to streamline your work in Excel 2010 ā€“ 2024 and Microsoft 365. The feature above is just one of many time-saving tools included.

šŸŒ Supports 40+ interface languages
āœ… Trusted by 500,000+ users and 80,000+ businesses worldwide
šŸš€ Compatible with all modern Excel versions
šŸŽ 30-day full-featured trial ā€” no registration, no limitations
Kutools for Excel RibbonKutools for Excel Ribbon