One of the most used functions in Excel is **the VLOOKUP function** and itâ€™s quite powerful too. We can make it more effective by using **the IF function** with the **VLOOKUP **function. We can apply those two functions together to perform various operations. In this article, we will use the example to show you the effectiveness of **VLOOKUP **with **Multiple IF Condition **in Excel.

## Introduction to Excel VLOOKUP Function

**Syntax**

**VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])**

**Arguments**

**lookup_value: **The value to look for in the leftmost column of the given table.

**table_array:** The table in which it looks for the **lookup_value** in the leftmost column.

**col_index_num:** The number of the column in the table from which a value is to be returned.

**[range_lookup]: **Tells whether an exact or partial match of the **lookup_value** is required. **0** for an exact match, **1** for a partial match. Default is **1** (**partial match**). This is optional.

## Introduction to Excel IF Function

**Syntax**

**IF(logical_test, [value_if_true], [value_if_false])**

**Arguments**

**logical_test: **Tests a logical operation.

**[value_if_true]: **If the logical operation is true, return this value.

**[value_if_false]: **If the logical operation is false, return this value.

## Example of VLOOKUP with Multiple IF Condition in Excel: 9 Criteria

### 1. Use VLOOKUP with IF Condition to Get Good or Bad

In our first example, weâ€™ll find out if a certain mark obtained by a student is good or bad. Therefore, follow the steps below to perform the task.

**STEPS:**

- First, select cell
**F5**. - Then, type the formula:

`=IF(VLOOKUP("Frank",B5:D8,2,FALSE)>80,"Great","Good")`

- Finally, press
**Enter**and itâ€™ll return the result.

🔎 **How Does the Formula Work?**

**VLOOKUP(“Frank”,B5:D8,2,FALSE)>80**

**The VLOOKUP function** searches for **Frank **in the range **B5:D8 **and returns the mark (**70**) in the **2nd **column. Eventually, it tests the mark if itâ€™s greater than **80 **or not.

**IF(VLOOKUP(“Frank”,B5:D8,2,FALSE)>80,”Great”,”Good”)**

**The IF function** returns **Good **as **70 **is not greater than **80**.

### 2. Apply VLOOKUP to Change Cut off Value with Multiple IF Condition in Excel

Now, we want to change the cut-off value or want to make it dynamic. Instead of specifying the value in the formula, weâ€™ll place the mark in cell **F4**. So, learn the steps in this **Example **to operate **VLOOKUP **with **Multiple IF Condition**Â in **Excel**.

**STEPS:**

- Firstly, select cell
**F6**. - Next, type the formula:

`=IF(VLOOKUP("Frank",B5:D8,2,FALSE)>F4,"Great","Good")`

- At last, press
**Enter**.

🔎 **How Does the Formula Work?**

**VLOOKUP(“Frank”,B5:D8,2,FALSE)>F4**

The **VLOOKUP **function seeks for **Frank **in the range **B5:D8 **and returns the mark (**70**) in the **2nd **column. Then, it tests the mark if itâ€™s greater than the **F4 **cell value (**65**) or not.

**IF(VLOOKUP(“Frank”,B5:D8,2,FALSE)>F4,”Great”,”Good”)**

Finally, The **IF **function returns **Great **as **70 **is greater than **65**.

**Read More: **How to Use PERCENTILE with Multiple IF Condition in Excel

### 3. Example to Get Discount Price Based on Retail Price with Multiple VLOOKUP & IF Conditions

In the below dataset, we have fixed retail prices for some items. But, weâ€™ll show you how to find out the discounted price with the **VLOOKUP **& **IF **functions. Hence, follow the process to know-how.

**STEPS:**

- In the beginning, select cell
**E5**. - Afterward, type the formula:

`=IF(VLOOKUP("Grape",B5:D8,3,FALSE)>150,VLOOKUP("Grape",B5:D8,3,FALSE)*80%)`

- In the end, press
**Enter**to return the value.

🔎 **How Does the Formula Work?**

**VLOOKUP(“Grape”,B5:D8,3,FALSE)>150**

The **VLOOKUP **function searches for **Grape **in the range **B5:D8 **and returns the price (**250**) in the **3rd **column. Next, it compares the price if itâ€™s greater than **150 **or not.

**VLOOKUP(“Grape”,B5:D8,3,FALSE)*80%**

This **VLOOKUP **function searches for **Grape **in the range **B5:D8 **and returns the price (**250**) in the **3rd **column. Next, it multiplies the value with **.8**.

**IF(VLOOKUP(“Grape”,B5:D8,3,FALSE)>150,VLOOKUP(“Grape”,B5:D8,3,FALSE)*80%)**

Lastly, the **IF **function returns **VLOOKUP(“Grape”,B5:D8,3,FALSE)*80% **output as **VLOOKUP(“Grape”,B5:D8,3,FALSE)>150** formula is true.

### 4. Combine Excel VLOOKUP, IF & ISNA Functions with Multiple Conditions

We will look for a certain fruit whether itâ€™s present or not in the dataset and if present, will return the price. Now, learn the **Example** for performing **VLOOKUP** with **Multiple** **IF** **Condition**Â in **Excel**.

**STEPS:**

- Select cell
**G4**at first. - Then, type the formula:

`=IF(ISNA(VLOOKUP(F4,B5:D8,3,FALSE)),"Not Present",VLOOKUP(F4,B5:D8,3,FALSE))`

- Lastly, press
**Enter**.

🔎 **How Does the Formula Work?**

**VLOOKUP(F4,B5:D8,3,FALSE)**

The **VLOOKUP **function seeks the **F4 **cell value (**Cherry**) in the range **B5:D8**.

**ISNA(VLOOKUP(F4,B5:D8,3,FALSE))**

**The ISNA function** looks for **VLOOKUP(F4,B5:D8,3,FALSE) **output to see if itâ€™s available or not.

**IF(ISNA(VLOOKUP(F4,B5:D8,3,FALSE)),”Not Present”,VLOOKUP(F4,B5:D8,3,FALSE))**

The **IF **function returns â€˜**Not Present**â€™ as **Cherry **is not available in the given dataset.

### 5. Example of Choosing the Best Store with VLOOKUP in Excel

Another use of the **VLOOKUP **function is that we can compare multiple stores to find out the best deal. Here, we have put **Shop 1 **in cell **G2**. Hence, follow the steps to carry out the operation.

**STEPS:**

- First of all, choose cell
**G5**to type the formula:

`=IF($G$2="Shop 1",VLOOKUP(F5,B5:D7,2,FALSE),VLOOKUP(F5,B5:D7,3,FALSE))`

- Subsequently, press
**Enter**and use**the AutoFill tool**to fill the rest.

🔎 **How Does the Formula Work?**

**VLOOKUP(F5,B5:D7,2,FALSE)**

The **VLOOKUP **function searches the **F5 **cell value (**Egg**) in the range **B5:D7 **and returns the value (**$1.50**) in the **2nd **column.

**VLOOKUP(F5,B5:D7,3,FALSE)**

This **VLOOKUP **function searches the **F5 **cell value (**Egg**) in the range **B5:D7 **and returns the value (**$1.75**) in the **3rd **column.

**IF($G$2=”Shop 1″,VLOOKUP(F5,B5:D7,2,FALSE),VLOOKUP(F5,B5:D7,3,FALSE))**

The **IF **function compares the **G2 **cell value (**Shop 1**) with â€˜**Shop 1**â€™. As itâ€™s true, the function returns **$1.50**. If the **G2 **cell value were **Shop 2**, itâ€™d have returned **$1.75**.

### 6. VLOOKUP Example with 2 Tables in Excel

So far we have used a single table to fetch data. In this example, weâ€™ll use **2 **tables as references. Therefore, learn the following steps of this **Example** to perform **VLOOKUP **in **2 Tables **with **Multiple IF Condition **in **Excel**.

**STEPS:**

- Select cell
**F6**. - Type the formula:

`=VLOOKUP(F5, IF(F4="January", B6:D7, B11:D12), 3, FALSE)`

- At last, press
**Enter**and itâ€™ll return the**Net Sales**of**Simon**.

🔎 **How Does the Formula Work?**

**IF(F4=”January”, B6:D7, B11:D12)**

The **IF** function compares the **F4 **cell value (**February**) with **January **and returns the range **B11:D12** as the logical test is false.

**VLOOKUP(F5, IF(F4=”January”, B6:D7, B11:D12), 3, FALSE)**

The **VLOOKUP **function seeks the **F5 **cell value (**Simon**) in the range **B11:D12 **and returns the **Net Sales **of **$12,500.00 **in the **3rd **column.

### 7. Excel VLOOKUP in IF Function Logical Test

Moreover, we can place the **VLOOKUP **function in the argument section of the** IF **function. See the following procedure to carry out the operation.

**STEPS:**

- First of all, choose cell
**G4**to type the formula:

`=IF(VLOOKUP(F4, B5:D8, 2, FALSE)="Available", "In Stock", "Not in Stock")`

- Next, press
**Enter**. Thus, youâ€™ll see the output.

🔎 **How Does the Formula Work?**

**VLOOKUP(F4, B5:D8, 2, FALSE)=”Available”**

The **VLOOKUP **function seeks the **F4 **cell value (**Grape**) in the range **B5:D8 **and compares the value in the **2nd **column (**Not Available**) with **Available**.

**IF(VLOOKUP(F4, B5:D8, 2, FALSE)=”Available”, “In Stock”, “Not in Stock”)**

Finally, the **IF **function returns **Not in Stock **as **VLOOKUP(F4, B5:D8, 2, FALSE)=”Available” **output is false.

**Read More: **Excel IF Function with 3 Conditions

### 8. Select Column of VLOOKUP Dynamically with IF Function

In this example, we want to create a dynamic column for the **VLOOKUP **function. For that reason, weâ€™ll make use of the **IF **function. Hence, go through the below **Example** to performÂ **VLOOKUP** with **Multiple** **IF** **Condition**Â in **Excel**.

**STEPS:**

- In the beginning, select cell
**C11**. Here, type the formula:

`=VLOOKUP(B11, B5:D8, IF($C$10="Physics", 2, 3), FALSE)`

- After that, press
**Enter**and itâ€™ll spill the data. Use**AutoFill**to complete the series.

🔎 **How Does the Formula Work?**

**IF($C$10=”Physics”, 2, 3)**

The **IF **function compares the **C10 **cell value (**Physics**) with **Physics **as given in the formula. Then, it returns **2 **as the logical test is true.

**VLOOKUP(B11, B5:D8, IF($C$10=”Physics”, 2, 3), FALSE)**

At last, the **VLOOKUP **function seeks the **B11 **cell value (**Wilham**) in the range **B5:D8 **and returns the value in the **2nd **column (**50**).

### 9. Example to Apply VLOOKUP for Dates with Multiple IF Condition in Excel

Additionally, we can apply **VLOOKUP **for dates. So, learn the steps of this **Example **to apply **VLOOKUP **for **Dates **with **Multiple IF Condition **in **Excel**.

**STEPS:**

- Click cell
**G4**. - Type the formula:

`=VLOOKUP(F4,IF((C5:C8>=F5)*(C5:C8<=F6),B5:D8,""),3,FALSE)`

- Lastly, press
**Enter**.

🔎 **How Does the Formula Work?**

**IF((C5:C8>=F5)*(C5:C8<=F6),B5:D8,””)**

The **IF **function compares each cell of the range **C5:C8 **with **F5 **and **F6 **cell values. Subsequently, it returns the range **B5:D8 **as the logical test is true.

**VLOOKUP(F4,IF((C5:C8>=F5)*(C5:C8<=F6),B5:D8,””),3,FALSE)**

Finally, the **VLOOKUP **function seeks the **F4 **cell value (**Grape**) in the range **B5:D8 **and returns the value in the **3rd **column (**Meena**).

## Alternative Example of VLOOKUP with Multiple IF Condition in Excel

### 1. Helper Column for Multiple Criteria in Excel

We can create a helper column for multiple criteria in **Excel**. Therefore, follow the steps to insert a helper column.

**STEPS:**

- First, select cell
**D5**. - Then, type the formula:

`=B5&"|"&C5`

- After that, press
**Enter**and itâ€™ll return the value. Use**AutoFill**to fill the series.

- Subsequently, select cell
**H5**to type the formula:

`=VLOOKUP($G5&"|"&H$4,$D$5:$E$8,2,0)`

- Press
**Enter**and use**AutoFill**to complete the rest.

Here, the **VLOOKUP **function looks for the **$G5&”|”&H$4 **cell value (**Wil|Mid**) in the range **$D$5:$E$8** and it returns **80 **present in the **2nd **column.

### 2. Multiple Criteria Example with CHOOSE Function

Weâ€™ll use **the CHOOSE function** along with **VLOOKUP **in this example to avoid the helper column. Learn the process to carry out the operation.

**STEPS:**

- Firstly, select cell
**H5**. - Next, type the formula:

`=VLOOKUP($G5&"|"&H$4,CHOOSE({1,2},$B$5:$B$8&"|"&$C$5:$C$8,$D$5:$D$8),2,0)`

- At last, press
**Enter**and itâ€™ll return the value.

🔎 **How Does the Formula Work?**

**CHOOSE({1,2},$B$5:$B$8&”|”&$C$5:$C$8,$D$5:$D$8)**

The **CHOOSE **function spills **B5:D8 **data into **2 **columns merging the cell values in the **B **and **C **columns together.

**VLOOKUP($G5&”|”&H$4,CHOOSE({1,2},$B$5:$B$8&”|”&$C$5:$C$8,$D$5:$D$8),2,0)**

The **VLOOKUP **function searches for **$G5&”|”&H$4 **in the range **B5:D8 **and returns **80 **present in the **2nd **column.

### 3. VLOOKUP Function with Two Conditions in Two Columns

Instead of using **VLOOKUP**, we can use **the INDEX MATCH formula** to pull the data based on **2 **conditions in **2 **separate columns. Hence, follow the procedure to perform the task.

**STEPS:**

- In the beginning, select cell
**H4**. - Afterward, type the formula:

`=INDEX(D5:D8,MATCH(1,(F4=B5:B8)*(G4=C5:C8),0))`

- In the end, press
**Enter**to return the value.

🔎 **How Does the Formula Work?**

**MATCH(1,(F4=B5:B8)*(G4=C5:C8),0)**

The **MATCH **formula returns **4 **to **INDEX **as the row number. Here, we compare the multiple criteria by applying boolean logic.

**INDEX(D5:D8,MATCH(1,(F4=B5:B8)*(G4=C5:C8),0))**

Lastly, the **INDEX **function returns **250 **which is in the **4th **row in the range **D5:D10**.

### 4. VLOOKUP for Multiple Columns in Excel

Weâ€™ll apply the **INDEX MATCH **formula for performing the lookup operation in multiple columns and returning the **Price **of the mentioned fruit. So, learn the steps below.

**STEPS:**

- Select cell
**G4**at first. - Then, type the formula:

`=INDEX(D5:D8,MIN(IF(B5:C8=F4,MATCH(ROW(B5:B8),ROW(B5:B8)),"")))`

- Lastly, press
**Enter**.

🔎 **How Does the Formula Work?**

**ROW(B5:B8)**

First, **the ROW function** returns the respective row numbers.

**MATCH(ROW(B5:B8),ROW(B5:B8))**

Then, the **MATCH **formula outputs are **1**, **2**, **3**, and **4**.

**IF(B5:C8=F4,MATCH(ROW(B5:B8),ROW(B5:B8)),””)**

The **IF **function compares each cell in **B5:C8 **with the **F4 **cell value and returns the values where it finds **TRUE **for the logical test.

**MIN(IF(B5:C8=F4,MATCH(ROW(B5:B8),ROW(B5:B8)),””))**

**The MIN function** returns the smallest value (**1**) out of the **IF(B5:C8=F4,MATCH(ROW(B5:B8),ROW(B5:B8)),””) **outputs.

**INDEX(D5:D8,MIN(IF(B5:C8=F4,MATCH(ROW(B5:B8),ROW(B5:B8)),””)))**

Eventually, the **INDEX **function returns **150 **which is in the **1st **row in the range **D5:D8**.

**Download Practice Workbook**

Download the following workbook to practice by yourself.

## Conclusion

Henceforth, you will be able to operate **VLOOKUP **with **Multiple IF Conditions **in Excel as shown in the examples. Keep using them and let us know if you have any more ways to do the task. Donâ€™t forget to drop comments, suggestions, or queries if you have any in the comment section below.

**<< Go Back to Multiple IF Condition in Excel | Excel IF Function | Excel Functions | Learn Excel**