VLOOKUP with multiple criteria (2023)

HeVLOOKUP function in Excelretrieves the first matching record and stops the search. Allows us to retrieve just onefetch-wertof the data table.VLOOKUP with multiple criteriahelps us get the information we need when thefetch-wertcontains multiple values.

For example, consider the following data in Excel.

We seek the sale value for the seller"Pedro' in the table arrayA2:C10, which occurs three times. Here it isVLOOKUP functionretrieved the first found”Pedro” Sale value, i.e.$ 6.045. The other 2 entries are not considered.

Now we're not sure which one."Pedro” Sale value we ask. So, to get an accurate result, let's look at the next column after the vendor column, i.e. h o "Ciudad" To divide.

Use ofVLOOKUP with various criteria, we can provide city name along with seller name.

Now we are looking for the seller.Pedro" out of town "melbourne“. However, this is not the correct way because we need a lookup column on the vendor and location combination in the table array.

VLOOKUP Multicriteria with help column

As seen in the example above, we do not have afetch-wertapply columnVLOOKUPsearch with multiple criteria. In these scenarios, we create an auxiliary column in the data table to support our various criteria.VLOOKUP.

VLOOKUP Multiple criteria with formulas and advanced functions

we can use thoseVLOOKUP Multiple CriteriaUsing some advanced features as follows:

1. Use of the ampersand to link multiple criteria together.
2. SELECT function to join multiple criteria in Excel.
3. MATCH function to include multiple criteria in Excel.
4. IF function to connect multiple criteria.
5. Multiple criteria in a column in Excel.
6. Using drop-down lists.

feature number Issue #1: Use ampersand to match multiple criteria

Let's consider another example of creating an auxiliary column, i.e. hfetch-wertcolumn with theVLOOKUP with multiple criteria.

The data below includes the brand of the cell phone, its version, the month and the number of units sold.

We have another table to get the sales unit values ​​based on the following requirement i.e. H"mobile market“, „execution",Y"Mes“.

First we have to create afetch-wertColumn with ampersand (&) symbol.

Insert a new column between columns C and D.

Use the ampersand (&) key to combine columns of criteria; H„Mobile Brand“, „Version“,Y"Eu".

[To use:For clarity in merged columns we use theRohr (|)symbol between each column.]

The steps to apply theVLOOKUP function,like himfetch-wertthe are column is created,

• Step 1:introduce theVLOOKUP functionand celaK2.

• Step 2:To select thefetch-wert,We need to select all criteria columns with the ampersand (&) symbol. However, before combining the columns, we need to combine themfetch-wertcells like thatfetch-wertColumn created in the table. we use itRohr (|)Symbol between individual criteria.

• Step 3:Select matrix from tableD2:E14.

• Stage 4:Enter column index number as2and type the match as an exact match by typingINCORRECTo0.

• Step 5:Close the clamp and press the "Prohibited” to get the matching result of multiple criteria using the ampersand symbol as shown below.

function # Problem #2: SELECT function to connect multiple criteria in Excel

we can use thoseSELECT Excel functionas an alternative to avoid inserting the auxiliary column.

the syntax ofSELECT functionAND,

Here we need to input the index number and it will select the specific position value.

Let's take the same data from the previous example.

For example, check out the example below.

In the index argument we specify 2 and in the value argument we specify the days of the week.

7 values ​​(days) are specified, and theSELECT functionselects the value in the second position of the value sequence, i.e. "Of“.

In the same way, we can combine them all.fetch-wertTable columns as follows.

In the index number argument of theSELECT function, we have given 1 and 2 in square brackets, i.e. h if we need to select multiple values ​​from multiple positions, we can use square brackets and specify index numbers.

so invale 1argument, we combine all criteria columns, i.e.„Mobile Brand“, „Version“,Y"Eu"columns.

so inWert2argument referred to in "units"Column. Hence theSELECT functionreturns the table as shown below.

We can use the same formula in the matrix argument of the data table.VLOOKUP functionto avoid creating the auxiliary column.

In the formula above, theSELECT functionreturns the table array as a concatenation offetch-wertsingle columns and columns. Then theVLOOKUP functionretrieves the array value from the table specified bySELECT function.

Feature #3 - MATCH Function to Include Multiple Criteria in Excel

Continuing with the previous example, we add another column to the data table, namely hsale value.

Now we need to allow the user to switch between calculations ofunitsYsale valuewith the same formula asExcel MATCH function.

MilitarySELECT function, we need to select another column, i.e. h osale valueTo divide.

In the index number, we specify {1,2,3}. The first is thefetch-wertthis is the concatenation of columns„Mobile Brand“, „Version“,Y"Eu".

Then we gave 2 value columns i.e."Units"Y"Sales Value".

Then theSELECT functionreturns the one from the required tableVLOOKUPthe matrix column of the table of .

Next, we need to dynamically get the column index number based on the column header.

Here it isCOMBINE functionreturns the column index number for theVLOOKUP functionbased on the column header provided by the user.

If we change the column header to Sales Amount, it will return the Sales Amount numbers as shown below.

Feature #4 - IF function to connect multiple criteria

HeExcel IF functionis also used to prevent the creation of the auxiliary column.

Consider the following data to determine retail value based on two criteria i.e. hsellerYCiudad.

In our example above, we used the auxiliary column to combine the Vendor and City columns to create a single column.fetch-wertTo divide. But now let's use thoseIF conditionto create a single column.

Consider the following image.

In the formula above, theIF conditiontests the city name entered in the cellF2and filter the matching rows from the tablespaceA2:C10.

Since it provides a single set of values, theVLOOKUPlooks up the salesperson in this table and returns the corresponding sales amount.

Let's write this inIF conditionin the table array argument of theVLOOKUP function.

We get the desired result without adding the auxiliary column as shown above.

Feature #5 - Multiple Criteria in a Single Column in Excel

Let's retrieve the first two corresponding values ​​for the multiplesearch_valueswe have, with theVLOOKUP with multiple criteria.

For example, consider the following requirement. We have two suppliers in the cells.E2YE3. For these two sellers, we need to find the first two cities.

The following formula gives us the required results.

Militaryfetch-wertArgument we select two columns, i.e.E2:E3, and in the array of the table we select the rangeA2:B10. HeVLOOKUP functionretrieves the first two matching records from the table array, as shown above.

Feature #6 - Using Dropdown Lists

To select what is neededfetch-wert, we can create drop-down lists to select thesearch_values.

We use the following data in Excel.

The steps to create a vendor dropdown list are:

• Step 1:select cellE2→ Select the "Data"tab → go to "data tools” group → click the button “expiration date” option drop-down menu → select the “Expiration date…" as shown below.

• Step 2:He "expiration dateThe window opens. At the "Ideasgo to tabvalidation criteria“,

• Select the "List” option in the dropdown list in the “To allow:"Up.
• Select thesellercellular area, that isA2:A4, soldier"Those ones:"Up.

• Step 3:click"ACCORDINGLY' to create a drop-down list of suppliers. Repeat the same for the city cell as well.

• Stage 4:Now apply the followingVLOOKUP functionin the G2 cell.

Using the drop-down list, we can select the combination ofVLOOKUP,it's himIF functionretrieves the value from the table.

important things to consider

• The help column adds unnecessary data to the table, which increases the file size when working with a large dataset.
• HeVLOOKUPretrieves the value only for the first matching record when searching for a criteria.
• If the correct match is not found, theVLOOKUP functionI will return#Error N/A.
• If we use Excel 2016 and earlier versions, we should run the formulas likeexcel array functionUse ofCtrl + Shift + EnterKey.
• ALT + A + V + Vis the keyboard shortcut to open the data validation window.

Frequently Asked Questions (FAQ)

download template

This article should help you understandVLOOKUP with multiple criteriawith its formulas and examples. You can download the template here to start using it right away.

Related Posts

This was a multi-criteria guide for VLOOKUP. Here we find research value with the top 6 methods, along with examples and a downloadable Excel template. You can get more information in the following articles:

• VLOOKUP with IF statement
• VLOOKUP with SUM
• VLOOKUP left