He

VLOOKUP 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 array**A2:C10**, which occurs three times. Here it is**VLOOKUP function**retrieved 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 of**VLOOKUP** **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.

##### table of contents

- What is multi-criteria VLOOKUP?
- VLOOKUP Multicriteria with help column
- feature number Issue #1: Use ampersand to match multiple criteria
- function # Problem #2: SELECT function to connect multiple criteria in Excel
- Feature #3 - MATCH Function to Include Multiple Criteria in Excel
- Feature #4 - IF function to connect multiple criteria
- Feature #5 - Multiple Criteria in a Single Column in Excel
- Feature #6 - Using Dropdown Lists

- important things to consider
- Frequently Asked Questions (FAQ)
- download template
- Related Posts

###### The central theses

- He
**VLOOKUP with multiple criteria**helps users to get required results for various search values. - Using the auxiliary column, we can combine multiple columns of criteria and find the corresponding value in the table.
- If we don't want helper columns, we can use the alternative methods using some Excel functions like
**CHOOSE(), DEPART(), IF() usw.** - He
**SELECT function**you can select multiple values based on the index number specified in braces. - We can combine multiple criteria or combinations of column values using the dropdown option in the data validation function.

### VLOOKUP Multicriteria with help column

As seen in the example above, we do not have a** fetch-wert**apply column

**VLOOKUP**search with multiple criteria. In these scenarios, we create an auxiliary column in the data table to support our various criteria.

**VLOOKUP**.

The steps to create a**assistant**column are

**Before the first column of the table, add a new column and name it "Helpers", i.e. column A.****Now we need to concatenate the Seller and City columns as shown below.**We first select the Salesperson column cell, then use the ampersand (&) symbol before selecting the City column cell. A new column was created with combined values from the Vendor and City columns. will it be him

column for the*fetch-wert***VLOOKUP function**.**Enter the VLOOKUP function in cell H2.****Now we need to select lookup_value, similar to the auxiliary column we created. Therefore, select the Seller and City column along with the ampersand (&) symbol.****Select the array of tables from A2:D10.****Enter column index number as 4 and range search as FALSE or 0 for exact match.****Close the bracket and press Enter to get the result.**now he

**VLOOKUP function**retrieves the sales amount based on various criteria. We see the sale value for the seller”**Pedro**"it's city"**melbourne**“, d.h.**7.850 $**.

### VLOOKUP Multiple criteria with formulas and advanced functions

we can use those**VLOOKUP Multiple Criteria**Using some advanced features as follows:

**Use of the ampersand to link multiple criteria together.****SELECT function to join multiple criteria in Excel.****MATCH function to include multiple criteria in Excel.****IF function to connect multiple criteria.****Multiple criteria in a column in Excel.****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. h** fetch-wert**column with the

**VLOOKUP 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 a** fetch-wert**Column 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 the**Rohr (|)**symbol between each column.]

The steps to apply the**VLOOKUP function,**like him** fetch-wert**the are column is created,

**Step 1:**introduce the**VLOOKUP function**and cela**K2**.

**Step 2:**To select theWe need to select all criteria columns with the ampersand (&) symbol. However, before combining the columns, we need to combine them*fetch-wert,*cells like that*fetch-wert*Column created in the table. we use it*fetch-wert***Rohr (|)**Symbol between individual criteria.

**Step 3:**Select matrix from table**D2:E14**.

**Stage 4:**Enter column index number as**2**and type the match as an exact match by typing**INCORRECT**o**0.**

**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 those**SELECT Excel function**as an alternative to avoid inserting the auxiliary column.

the syntax of**SELECT function**AND,

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 the**SELECT function**selects the value in the second position of the value sequence, i.e. "**Of**“.

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

In the index number argument of the**SELECT 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 in**vale 1**argument, we combine all criteria columns, i.e.**„Mobile Brand“, „Version“,**Y**"Eu"**columns**.**

so in**Wert2**argument referred to in "**units**"Column. Hence the**SELECT function**returns the table as shown below.

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

In the formula above, the**SELECT function**returns the table array as a concatenation of** fetch-wert**single columns and columns. Then the

**VLOOKUP function**retrieves the array value from the table specified by

**SELECT 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 h**sale value.**

Now we need to allow the user to switch between calculations of**units**Y**sale value**with the same formula as**Excel MATCH function**.

Military**SELECT function**, we need to select another column, i.e. h o**sale value**To divide.

In the index number, we specify {1,2,3}. The first is the** fetch-wert**this is the concatenation of columns

**„Mobile Brand“, „Version“,**Y

**"Eu".**

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

Then the**SELECT function**returns the one from the required table**VLOOKUP**the matrix column of the table of .

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

Here it is**COMBINE function**returns the column index number for the**VLOOKUP function**based 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

He**Excel IF function**is also used to prevent the creation of the auxiliary column.

Consider the following data to determine retail value based on two criteria i.e. h**seller**Y**Ciudad**.

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

**IF condition**to create a single column.

Consider the following image.

In the formula above, the**IF condition**tests the city name entered in the cell**F2**and filter the matching rows from the tablespace**A2:C10**.

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

Let's write this in**IF condition**in the table array argument of the**VLOOKUP 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 multiple** search_values**we have, with the

**VLOOKUP with multiple criteria.**

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

The following formula gives us the required results.

Military** fetch-wert**Argument we select two columns, i.e.

**E2:E3**, and in the array of the table we select the range

**A2:B10**. He

**VLOOKUP function**retrieves the first two matching records from the table array, as shown above.

#### Feature #6 - Using Dropdown Lists

To select what is needed** fetch-wert**, we can create drop-down lists to select the

*search_values.*We use the following data in Excel.

The steps to create a vendor dropdown list are:

**Step 1:**select cell**E2**→ 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 date**The window opens. At the "**Ideas**go to tab**validation criteria**“,

- Select the "
**List**” option in the dropdown list in the “**To allow:**"Up. - Select the
**seller**cellular area, that is**A2: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 following**VLOOKUP function**in the G2 cell.

Using the drop-down list, we can select the combination of**VLOOKUP,**it's him**IF function**retrieves 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.
- He
**VLOOKUP**retrieves the value only for the first matching record when searching for a criteria. - If the correct match is not found, the
**VLOOKUP function**I will return**#Error N/A**. - If we use Excel 2016 and earlier versions, we should run the formulas likeexcel array functionUse of
**Ctrl + Shift + Enter**Key. **ALT + A + V + V**is the keyboard shortcut to open the data validation window.

### Frequently Asked Questions (FAQ)

**1. How to VLOOKUP with multiple criteria using index and match?**

matching**INDEX and MATCH functions**is used as an alternative to**VLOOKUP function**, and also to meet various criteria.

For example, consider the following data in Excel.

select cell**J2**, enter the formula**=ÍNDICE(D2:D14,VERGLEICH(1,(G2=A2:A14)*(H2=B2:B14)*(I2=C2:C14),0)),**and pressure"**Prohibited**“.

Retrieves the value of the corresponding combination of all criteria.** fetch-wert**It is given.

**2. How to use VLOOKUP with multiple criteria in different worksheets?**

To get values from different worksheets, we need to create an auxiliary column in the worksheet where the table array will be.

**3. What are the limitations of using VLOOKUP with multiple criteria?**

Some of the limitations of**VLOOKUP with multiple criteria**Son:**•**If we create an auxiliary column, this column must be just to the left of the result column.**•**The auxiliary column increases the file size on large datasets.

**4. Why doesn't VLOOKUP work with multiple criteria?**

If the multicriteria formula does not give the desired result, we have to look at the** fetch-wert**and the concatenation style.

### download template

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

VLOOKUP with multiple criteriaTo go down

### 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