VLOOKUP with Multiple Criteria in Excel (6 Examples)

Get FREE Advanced Excel Exercises with Solutions!

VLOOKUP function is generally used to look for a value in the leftmost column in a table and the function will return a value in the same row from the specified column. In this article, you will get to learn how you can use this VLOOKUP function for multiple criteria in Excel with some suitable examples and illustrations.


1. Using Ampersand to Join Multiple Criteria in VLOOKUP in Excel

In the following dataset, there are some data of smartphone models of three popular brands. Column B represents a helper column which is the combination of the values alongside Column C and Column D.

Using Ampersand to Join Multiple Criteria in VLOOKUP in Excel

As the VLOOKUP function looks for a value in the first column, we have to keep this helper column in first place in the following table. By using Ampersand (&), the VLOOKUP function in this example will look for a text value in Column B which is the combination of a specified brand and its corresponding model number.

For example, we’re going to know the price of the Samsung S21 Ultra. So, here are two different criteria: the brand name and the model number of the smartphone. Now we’ll use the VLOOKUP function to extract the price of the specified smartphone.

In the output Cell C18, the required formula will be:

=VLOOKUP(D15&D16,B5:G13,6,FALSE)

After pressing Enter, you’ll be shown the price of the specified smartphone at once.

Using Ampersand to Join Multiple Criteria in VLOOKUP in Excel


2. Combining VLOOKUP with CHOOSE Function to Join Multiple Criteria in Excel

If you don’t want to use a helper column to extract data with VLOOKUP under multiple criteria then you can combine VLOOKUP with CHOOSE function instead. The CHOOSE function chooses a value or action to perform from a list of values, based on its index number. The generic formula of this CHOOSE function is:

=CHOOSE(index_num, value1, [value2],…)

As we’re going to know the price of the Samsung S21 Ultra from the table, so the required formula in Cell C18 will be:

=VLOOKUP($D$15&$D$16,CHOOSE({1,2},$B$5:$B$13&$C$5:$C$13,$F$5:$F$13),2,FALSE)

After pressing Enter, you’ll find the price of the mentioned smartphone model right away.

VLOOKUP with CHOOSE Function to Join Multiple Criteria in Excel

In this formula, CHOOSE function forms a table with Columns B, C, and F. As Columns B and C have been merged inside the CHOOSE function, they will represent a single column for the VLOOKUP function here.

Read More: Vlookup with Multiple Criteria without a Helper Column in Excel


3. Joining VLOOKUP with MATCH Function to Include Multiple Criteria in Excel

The MATCH function returns a relative position of an item in an array that matches a specified value in a specified order. By combining the VLOOKUP with the MATCH function here, we can specify the output types manually.

The required formula in Cell C18 will be now:

=VLOOKUP(D15&D16,B5:G13,MATCH(B18,B4:G4,0),FALSE)

After pressing Enter, you’ll see the price of the specified smartphone model.

VLOOKUP with MATCH Function to Include Multiple Criteria in Excel

In this formula, the MATCH function looks for the value present in Cell B18 in the array of B4:G4 and then returns the column number. And this column number is then assigned to the third argument (col_num_index) of the VLOOKUP function.

So, now if you change the output type in Cell B18, the corresponding result in Cell C18 will be updated at once. For example, if you type Chipset in Cell B18, the embedded formula in Cell C18 will be shown the chipset name for the specified smartphone model.

VLOOKUP with MATCH Function to Include Multiple Criteria in Excel

Read More: Use VLOOKUP with Multiple Criteria in Different Sheets


4. Combining VLOOKUP with IF Function to Join Multiple Criteria

There is another method to avoid a helper column while using the VLOOKUP function with multiple criteria in Excel. We have to use the IF function here to define the lookup array for the VLOOKUP function.

Since we’re using a similar dataset here, the required formula in Cell C18 to pull out the price of the Samsung S21 Ultra will be:

=VLOOKUP(D15, IF(C5:C13=D16, B5:F13, ""), 5, FALSE)

Press Enter and the formula will return the price of the specified smartphone model instantly.

Combining VLOOKUP with IF Function to Join Multiple Criteria


5. Using the VLOOKUP Function with Multiple Criteria in a Single Column in Excel

In this section, we’ll see how the VLOOKUP function works by looking for multiple values in a single column. We have to input a range of cells in the first argument (lookup_value) of the VLOOKUP function here.

For example, we want to draw out two smartphone model numbers of Apple and Xiaomi brands. So, the required formula in the output Cell C18 should be:

=VLOOKUP(D15:D16,B5:F13,2,FALSE)

After pressing Enter, you’ll get two model numbers of Apple and Xiaomi smartphones.

VLOOKUP Function with Multiple Criteria in Single Column in Excel

As the VLOOKUP function always extracts the first matched data, in this example, only the first model numbers of Apple and Xiaomi brands have appeared as return values.

Read More: How to Use VLOOKUP with Multiple Criteria in Different Columns


6. Using Drop-Down Lists as Multiple Criteria in VLOOKUP

We can also create the drop-down lists for the VLOOKUP function. After creating the drop-down lists, you don’t have to input the criteria manually anymore. Rather, you’ll select the smartphone brand and model number from the drop-down lists and the VLOOKUP function will display the price for the corresponding smartphone model.

In the following dataset, we’ll now create two drop-down lists for smartphone brands and model numbers in Cells D15 and D16 respectively.

Use of Drop-Down Lists as Multiple Criteria in VLOOKUP

📌 Step 1:

➤ Select Cell D15 first.

➤ Under the Data ribbon, choose the Data Validation option from the Data Tools drop-down.

A dialogue box will appear.

Use of Drop-Down Lists as Multiple Criteria in VLOOKUP

📌 Step 2:

➤ In the Allow box, select the List option.

➤ Enable editing in the Source box and select the range of cells B5:B13.

➤ Press OK.

Use of Drop-Down Lists as Multiple Criteria in VLOOKUP

So, the first drop-down list for the smartphone brands is now ready to use.

Use of Drop-Down Lists as Multiple Criteria in VLOOKUP

📌 Step 3:

➤ Similarly, you have to create another drop-down list for the smartphone model numbers. You have to select the range of cells C5:C13 in the Source option of the Data Validation dialogue box here.

Use of Drop-Down Lists as Multiple Criteria in VLOOKUP

📌 Step 4:

➤ Now in Cell 18, enter the following formula:

=VLOOKUP(D15, IF(C5:C13=D16, B5:F13, ""), 5, FALSE)

➤ From the drop-down list, choose a smartphone brand and its model number and you’ll be shown the price of the selected smartphone model.

Every time you select a new smartphone brand and its model from the drop-down lists, the output cell with the price will get updated.

Use of Drop-Down Lists as Multiple Criteria in VLOOKUP

Read More: Excel VLOOKUP with Multiple Criteria in Column and Row


Download Practice Workbook

You can download the Excel workbook that we’ve used to prepare this article.


Concluding Words

I hope the examples described above will now help you to apply them in your Excel spreadsheets while using the VLOOKUP function with multiple criteria. If you have any questions or feedback, please let me know in the comment section. Or you can check out our other articles related to Excel functions on this website.


VLOOKUP with Multiple Criteria: Knowledge Hub


<< Go Back to Excel VLOOKUP Function | Excel Functions | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Nehad Ulfat
Nehad Ulfat

NEHAD ULFAT is an excellent marine engineer who loves working with Excel and diving into VBA programming. For him, programming is like a tool that saves time when dealing with data, files, and the internet. His skills go beyond the basics, including ABACUS, AutoCAD, Rhinoceros, Maxsurf, and Hydromax. He got his B.Sc in Naval Architecture & Marine Engineering from BUET but switched gears, working as a content developer.  In this role, he creates techy content all about Excel... Read Full Bio

4 Comments
  1. Can I use lookup to populate the cells of one spreadsheet with the information from another using two separate criteria(date and text). I’ve tried using Vlookup but I keep having issues with the date. Google sheets keep misinterpreting it.

    • Hello, TEMITOPE!

      You have asked a nice thing. Using VLOOKUP with dates is a complex thing sometimes and may result in errors easily. In this regard, you need to use the TEXT function.
      Suppose, you have 5 columns in your table. You want the fifth column value for the C16 text and C15 date value. The date column is in the C5:C13 cells. In this situation, you can use the following formula for using VLOOKUP with text and date.

      =VLOOKUP(C16,IF(TEXT(C5:C13,"mm/dd/yy")=TEXT(C15,"mm/dd/yy"), B5:F13, ""), 5, FALSE)

      Thank you for your query. We appreciate it so much.
      Regards,
      Tanjim Reza

  2. Hello,

    We have to lookup a percentage in a table, corresponding with a year.
    Then we have then to multiply a value with this percentage.
    We have to do this until the current year.

    Can we use vlookup with a multiplication?

    • Hello, W BREKVELD!
      Thank you for your query.

      Yes, you can multiply a value with the looked-up percentage corresponding with a year. Just use the VLOOKUP function properly. Then write your desired multiplier in the formula bar before the used VLOOKUP function and then insert an asterisk (*) symbol.
      Thus, the value will be multiplied by the looked-up percentage. And, to do this until the current year, use your fill handle feature to copy the same formula.

      Regards,
      Tanjim Reza

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo