Khan Muhammad Kawser
VLOOKUP is one of the most powerful and top-used functions in Excel. Using IF logical function with VLOOKUP makes the formulas more powerful. In this article, we shall see a good number of examples where we have paired Excel VLOOKUP Function with IF condition.
Excel IF Function
Check whether a condition is met, and return one value if TRUE, and another value if FALSE.
The Syntax of IF function:
IF (logical_test, value_if_true, [value_if_false])logical_test (required)
The condition you want to test
value_if_true (required)
If the logical_test is TRUE, the IF function will return this value.
value_if_false (optional)
If the logical_test is FALSE, the IF function will return this value.
Excel VLOOKUP Function
Looks for a value in the leftmost column of a table, and then returns a value in the same row from a column you specify. By default, the table must be ordered in ascending order.
The Syntax of VLOOKUP function:
VLOOKUP (lookup_value, table_array, column_index_num, [range_lookup])lookup_value (required)
This indicates the value you want to lookup using this function. Make sure the lookup_value is in the 1st column of your table_array.
table_array (required)
It is the Cell range from where you want to lookup a value.
col_index_num (required)
It is the column number of your given given cell range starting with 1 from the leftmost column.
range_lookup (optional)
This is an optional logical value that indicates if you want to find an approximate match or an exact match using this function.
TRUE will look for the closest value after considering that the first column of the table is ordered either numerically or alphabetically.
If you don’t indicate a method, this will be used by default.
FALSE will look for the first column’s precise value.
Download Practice Workbook
7 Ways to Use VLOOKUP Function with IF Condition in Excel
Here, you will find 7 different ways with real-life examples to use the VLOOKUP function with IF condition in Excel.
1. Using VLOOKUP Function with IF Condition to Return In Stock/ Not in Stock in Excel
This is a good example if you manage inventory using Excel. In the following worksheet (top left corner), you’re seeing I have a table. The table lists some Products and their status under the Availability column.
Now, we will use the VLOOKUP function with the IF condition to return In Stock or Not in Stock in the 2nd Table.
Here are the steps.
Steps:
- After that, in the 2nd table (under the Shopping Cart), under the Status column, and in Cell C13 we will input this formula.
=IF(VLOOKUP(B13, product_status, 2, FALSE)="Available", "In Stock", "Not in Stock")
This formula is also self-explanatory from the above image. Here is the explanation for new Excel users:
Now, let’s explain the logical_test argument of this formula. We are using this formula VLOOKUP(B14, product_status, 2, FALSE)=”Available” as the IF function’s logical_test argument. If this part of the formula returns a TRUE value then the cell will show the “In Stock” value, otherwise it will show a “Not in Stock” value.
- Then, press ENTER and drag down the Fill Handle tool to AutoFill the formula for the rest of the cells.
We have also used another IF and VLOOKUP combo under the Price $ column.
- Now, this is the formula I have used in cell E13.
=IF(C13="In Stock", D13*VLOOKUP(B13,product_status,3, FALSE), "Coming soon...")
Here, if the value of cell C13 is “In Stock”, then the cell will show the value of this part of the formula: D13*VLOOKUP(B13,product_status,3, FALSE). This formula is just a product of the D13 cell value and a simple VLOOKUP formula.
If the value of cell C13 is not “In Stock”, then the cell will show this value “Coming soon…”.
- After that, press ENTER and drag down the Fill Handle tool to AutoFill the formula for the rest of the cells.
- Next, insert the following formula in Cell E17.
=SUM(E13:E16)
- Finally, press ENTER.
Read More: Return the Highest Value Using VLOOKUP Function in Excel
2. Using VLOOKUP Function with IF Condition for 2 Tables of Values
In this example, you will see how to use two or more table arrays in the Excel VLOOKUP formula.
Steps:
- Firstly, insert the following formula in Cell H5.
=VLOOKUP(G5, IF(F5="New", new_customer, old_customer), 2, TRUE)
Formula Breakdown
- Firstly, Cell G5 is the lookup_value in the VLOOKUP Function and it is an amount under the Sales column.
- Now, IF(F5=”New”, new_customer, old_customer): This formula will return one of the two tables: new_customer and old_customer. new_customer = $B$5:$C$9 and old_customer = $B$13:$C$17.
- After that, the rest is simple. The column index number is 2. So, the VLOOKUP function will return the value from the 2nd column of the same row where it finds the lookup value.
We have used the TRUE value as the range_lookup argument, so the VLOOKUP function will search for the closest value equal to or less than the lookup value.
- Then, press ENTER and drag down the Fill Handle tool to AutoFill the formula for the rest of the cells.
- Finally, you will get all the values of Comm% using the VLOOKUP function with IF condition from 2 tables.
Read More: What Is Table Array in Excel VLOOKUP?
3. Using Data Validation Feature with VLOOKUP Function and IF Condition
Now, we will show you how to use the Data Validation Feature with the VLOOKUP function and IF condition in Excel.
Here, we have a dataset containing the Product list and the Price of two stores Meena and Lavender. Now, we will show you how to VLOOKUP this data in the 2nd table.
Follow the steps given below to do it yourself.
Steps:
- Firstly, select Cell C4.
- Then, go to the Data tab >> click on Data Tools >> click on Data Validation >> select Data Validation.
- Now, the Data Validation box will appear.
- After that, select List as Allow and insert Cell range C6:D6 as Source.
- Next, click on OK.
- Again, select Cell C4.
- Then, click on the Drop-down button.
- Now, select any Store of your choice. Here, we will select Meena.
- Next, name Cell range B7:D111 as shop_price going through the steps shown in Method1.
- After that, select Cell G7 and insert the following formula.
=IF($C$4="Meena",VLOOKUP(F7,shop_price,2,FALSE),VLOOKUP(F7,shop_price,3,FALSE))
Formula Breakdown
- In the beginning, the IF Function tests whether $C$4 cell value is equal to the value Meena.
- Then, if the above logical test is TRUE, it returns this part of the formula VLOOKUP(F7, shop_price,2, FALSE). It is a straightforward VLOOKUP formula. It searches for the value of cell F7 in the shop_price table array and if it finds it then returns the value of the 2nd column of the same row.
- Otherwise, if the logical test is FALSE, then it returns this part of the formula VLOOKUP(F7, shop_price,3, FALSE). A simple VLOOKUP formula. VLOOKUP finds the value of the F7 cell in the shop_price table array and if it finds it then returns the value of the 3rd column of the same row.
- Then, press ENTER and drag down the Fill Handle tool to AutoFill the formula for the rest of the cells.
- Now, you will get all the Price values of the Products of the Meena store.
- Next, select Cell I7 and insert the following formula.
=G7*H7
Here, in the formula, we multiplied Cell G7 with the value of Cell H7 to the Total price of the Product.
- Then, press ENTER and drag down the Fill Handle tool to AutoFill the formula for the rest of the cells.
- Now, we will get all the Total prices of the individual Products.
- After that, select Cell I12 and insert the following formula.
=SUM(I7:I11)
Here, in the SUM Function, we added all the values of the Cell range I7:I11.
- Finally, press ENTER.
Read More: How to Use VLOOKUP with Multiple Conditions in Excel
4. Selecting Col Index Num Argument of VLOOKUP Function Dynamically with IF Function
In the fourth method, we will show you how you can select the Col Index Num argument of the VLOOKUP function dynamically with the IF function in Excel.
Here are the steps.
Steps:
- In the beginning, name Cell range B4:E11 as sales_table going through the steps shown in Method1.
- Then, create a drop-down button in Cell C14 using the Data Validation feature where insert Cell range D4:E4 as Source going through the steps shown in Method3.
- After that, select any option using the drop-down button. Here, we will select Projected.
- Next, select Cell C17 and insert the following formula.
=VLOOKUP(B17, sales_table, 2, FALSE)
Here, in the VLOOKUP Function, we inserted Cell B7 as lookup_value, sales_table named range as table_array, 2 as col_index_num, and FALSE as range_lookup.
- Now, press ENTER and drag down the Fill Handle tool to AutoFill the formula for the rest of the cells.
- Then, select Cell C24 and insert the following formula.
=SUM(C17:C23)
Here, in the SUM Function, we added the values of Cell range C17:C23 to get the amount of Total Target.
- After that, press ENTER.
- Next, select Cell D16 and insert the following formula.
=VLOOKUP(B16, sales_table, IF($C$14="Projected", 3, 4), FALSE)
This is a straightforward simple VLOOKUP formula. We have just made the col_index_num argument part dynamic using an IF function.
This part of the formula demands a little discussion: IF($C$14=”Projected”, 3, 4). If the Cell $C$14 value is equal to the value Projected, the IF function will return 3, otherwise, it will return 4. So, this is dynamically selecting the column index number of the VLOOKUP formula.
- Afterward, press ENTER and drag down the Fill Handle tool to AutoFill the formula for the rest of the cells.
- Then, select Cell D24 and insert the following formula.
=SUM(D17:D23)
Here, in the SUM Function, we added the values of Cell range D17:D23 to get the amount of Total Projected.
- Finally, press ENTER.
Read More: How to Use Dynamic VLOOKUP in Excel (3 Easy Ways)
Similar Readings
- SUMIF and VLOOKUP Functions Across Multiple Sheets in Excel
- Excel VLOOKUP to Find Last Value in Column (with Alternatives)
- Combine SUMIF and VLOOKUP in Excel (3 Quick Approaches)
- VLOOKUP with Numbers in Excel (4 Examples)
- XLOOKUP vs VLOOKUP in Excel (Comparative Analysis)
5. Using ISNA and IFERROR Function with VLOOKUP Functions and IF Condition in Excel
These two techniques will help you to handle #N/A errors. VLOOKUP generates a #N/A error when it does not find the value that you’re looking for.
Now, look at the following image intensively. Here, Cell F6 shows the #N/A error as we did not handle the error smartly.
Follow the Steps given below to solve this error using the ISNA and IFERROR functions in Excel.
Steps:
- Firstly, select Cell F7 and insert the following formula.
=IFERROR(VLOOKUP(E7,price_list,2,FALSE),"Not found")
Here, as the value of the IFERROR function, we have input the VLOOKUP formula. If this VLOOKUP formula returns an error, then the “Not found” value will be shown in the F7 cell.
- Then, press ENTER.
- Now, you can see that the error has been removed.
- After that, to remove the error using the ISNA function select Cell F8 and insert the following formula.
=IF(ISNA(VLOOKUP(E8,price_list,2,FALSE)), "Not found", VLOOKUP(E8, price_list, 2, FALSE))ISNA function returns TRUE when it finds the #N/A error. I have shown it on the top right corner of the worksheet.
If this formula returns the #N/A error, then ISNA will return a TRUE value, and IF function’s logical_test argument will be TRUE. If this VLOOKUP formula returns a real value, ISNA will return a FALSE value.
So, if ISNA returns the TRUE value IF function this value “Not found” will be shown in cell F8. Otherwise, this formula will be executed: VLOOKUP(E8, price_list, 2, FALSE). This is a straightforward VLOOKUP formula.
- Finally, press ENTER to remove the error using the ISNA function.
Read More: How to Use IF ISNA Function with VLOOKUP in Excel
6. Performing Multiple Calculations by Using VLOOKUP Function with IF Condition
Next, we will show you how to perform multiple calculations by using the VLOOKUP Function with the IF condition.
Here, we will select any Salesman, and depending on the Sales value we will calculate Comm% using the VLOOKUP Function with IF condition.
Follow the steps given below to it on your own.
Steps:
- Firstly, create a drop-down button in Cell G4 using the Data Validation feature where insert Cell range B5:B9 as Source going through the steps shown in Method3.
- Next, select any Salesman from the drop-down list. Here, we will select Salesman A.
- Then, select Cell G5 and insert the following formula.
=IF(VLOOKUP(G4,$B$5:$D$9,3,FALSE)>=150,VLOOKUP(G4,$B$5:$D$9,3,FALSE)*30%,VLOOKUP(G4,$B$5:$D$9,3,FALSE)*15%)
Formula Breakdown
- Firstly, in the IF function, we set VLOOKUP(G4,$B$5:$D$9,3,FALSE)>=150 as logical_test. It will check if the value in Cell G4 is greater than or equal to 150 by using the VLOOKUP Function in Cell range B5:D9 and in the 3rd column.
- Then, if the function returns TRUE, it will find the value of Sales from Cell range B5:D9 and in the 3rd column using the VLOOKUP Function and after that multiply it with 30%.
- Otherwise, it will multiply the VLookup value by 15%.
- Finally, press ENTER to get the value of Comm%.
Read More: VLOOKUP with Multiple Criteria in Excel (6 Examples)
7. Comparing Vlookup Value with Another Cell Value
In the final method, we will show you how to compare the Vlookup value with another cell value using the VLOOKUP Function with the IF condition.
Firstly, we will calculate the Max Sales value and then check if the product in Cell G5 is Max or not.
Go through the steps given below to do it on your own.
Steps:
- In the beginning, select Cell F4 and insert the following formula.
=MAX(D5:D9)
Here, in the MAX function, we inserted Cell range D5:D9 as a number to identify the maximum value.
- Then, press ENTER.
- After that, create a drop-down button in Cell G5 using the Data Validation feature where you insert Cell range C5:C9 as Source going through the steps shown in Method3.
- Next, select any Product from the drop-down list. Here, we will select Egg.
- Now, select Cell I5 and insert the following formula.
=IF(VLOOKUP($G$5,$C$5:$D$9,2,FALSE)>=$G$4,"Yes","No")
Formula Breakdown
- Firstly, in the IF function, we set VLOOKUP($G$5,$C$5:$D$9,2,FALSE)>=$G$4 as logical_test. It will check if the value in Cell G5 is greater than or equal to the value in Cell G4 by using the VLOOKUP Function in Cell range C5:D9 and in the 2nd column.
- Then, if the function is TRUE, it will return “Yes”.
- Otherwise, if the function is FALSE, it will return “No”.
- Finally, press ENTER.
Read More: Find Max of Multiple Values by Using VLOOKUP Function in Excel
Practice Section
In this section, we are giving you the dataset to practice on your own and learn to use these methods.
Conclusion
So, in this article, you will find 7 ways to use the VLOOKUP Function with IF Condition in Excel. Use any of these ways to accomplish the result in this regard. Hope you find this article helpful and informative. Feel free to comment if something seems difficult to understand. Let us know any other approaches which we might have missed here. And, visit ExcelDemy for many more articles like this. Thank you!
In the 4th way, I commonly use MATCH instead IF, because if I have more than two items, MATCH works very well
The col_index argument will be like this:
MATCH($C$14,$B$4:$E$4,0)
By the way, great job!! Keep going!
Have a nice day!
Hi Daniel,
Thanks for your feedback.
Yes. INDEX and MATCH combo work in a more versatile way than the VLOOKUP function. I did not use Index Match because I wanted to show all the examples with VLOOKUP and IF Functions.
Keep in touch.
Best regards
Kawser
interesting function & I can’t dispense it <3
Thanks for your feedback 🙂
Congratulations, great!
I want to user function which you shown in screen in VBA code , but it’s not run.
I m using 3 different sheet.
1 sheet lookup value pick d2 col and also check f2 value .
Range another sheet2 case 1 and case 2,value need
And results sheet3
Thanks, Ferreira!
Thank you for the useful tutorial, it explained the uses very easily and effectively. 🙂
Nice code, but your explanations are rather condescending. “For the complete layman”…? Comes off as superior.
Maybe a little bit tough for the complete layman. I shall change my Phrase then 🙂
Hi,
I’m struggling to find the right formula to multiply units by rates.
I have different materials and tasks with different units and rates are depend on quantities. Some of the units only have one rate with no conditions.
I have a more than 2000 row spreadsheet and units also varies that means that the formula also need to find the unit on sheet 1. Rate criteria can also change on sheet 1.
I’m looking for the price on Sheet2.
I believe the below formula need to be combined with vlookup but I cannot get it to work
‘=IF(C210,C250,C2200,C2*G2,0))))
Many thanks for your help!
Niki
Sheet 1
Unit”Rate1(not exceeding)””Rate2(not exceeding)””Rate3(not exceeding)””Rate4(exceeding)”
day
h
m (QB) 10 50 200 200
m
m2 (QB) 10 50 150 150
m2
Sheet 2
Description Unit Quantity Rate 1 Rate 2 Rate 3 Rate 4 Price
path m (QB) 10 11 13 13.5 14
road m (QB) 51 5 10 15 20
wall m2 (QB) 35 10 15 20 25
wood m 20 11
paint m2 150 12
Is there a way to add another criteria to this formula? I want to add if this range has a certain word, in my case OUTSTANDING, to return BLANK””. Current Formula: IFERROR(VLOOKUP(B9,’REPORT!A:C,3,FALSE),””)
Hi there!
I couldn’t fully understand what you need from your comment. However, I assumed you may want to create something like the following formula.
=IFERROR(IF(REPORT!A:C="OUTSTANDING","",VLOOKUP(B9,REPORT!A:C,3,FALSE)),"")Is this what you needed? If not, then tell us more about the problem so that we may help you. Thank you for being with us.
Regards
Md. Shamim Reza (ExcelDemy Team)