by The tutorial shows how to combine VLOOKUP and IF function together to v-lookup with if condition in Excel. You will also learn how to use IF ISNA VLOOKUP formulas to replace #N/A errors with your own text, zero or blank cell.
Whilst the VLOOKUP and IF functions are useful on their own, together they deliver even more valuable experiences. This tutorial implies that you remember the syntax of the two functions well, otherwise you may want to brush up on your knowledge by following the above links.
Vlookup with If statement: return True/False, Yes/No, etc.
One of the most common scenarios when you combine If and Vlookup together is to compare the value returned by Vlookup with a sample value and return Yes / No or True / False as the result.
In most cases, the following generic formula would work nicely:
Translated in plain English, the formula instructs Excel to return True if Vlookup is true (i.e. equal to the specified value). If Vlookup is false (not equal to the specified value), the formula returns False.
Below you will a find a few real-life uses of this IF Vlookup formula.
Example 1. Look up a specific value
Let's say, you have a list of items in column A and quantity in column B. You are creating a dashboard for your users and need a formula that would check the quantity for an item in E1 and inform the user whether the item is in stock or sold out.
You pull the quantity with a regular Vlookup with exact match formula like this:
=VLOOKUP(E1,$A$2:$B$10,2,FALSE)
Then, write an IF statement that compares Vlookup's result with zero, and returns "No" if it is equal to 0, "Yes" otherwise:
=IF(VLOOKUP(E1,$A$2:$B$10,2,FALSE)=0,"No","Yes")
Instead of Yes/No, you can return TRUE/FALSE or In Stock/Sold out or any other two choices. For example:
=IF(VLOOKUP(E1,$A$2:$B$10,2)=0,"Sold out","In stock")
You can also compare the value returned by Vlookup with sample text. In this case, be sure to enclose a text string in quotation marks, like this:
=IF(VLOOKUP(E1,$A$2:$B$10,2)="sample text",TRUE,FALSE)
Example 2. Compare Vlookup result with another cell
Another typical example of Vlookup with If condition in Excel is comparing the Vlookup output with a value in another cell. For example, we can check if it's greater than or equal to a number in cell G2:
=IF(VLOOKUP(E1,$A$2:$B$10,2)>=G2,"Yes!","No")
And here is our If formula with Vlookup in action:
In a similar fashion, you can use any other logical operator together with a cell reference in your Excel If Vlookup formula.
Example 3. Vlookup values in a shorter list
To compare each cell in the target column with another list and return True or Yes if a match is found, False or No otherwise, use this generic IF ISNA VLOOKUP formula:
If Vlookup results in the #N/A error, the formula returns "No", meaning the lookup value is not found in the lookup list. If the match is found, "Yes" is returned. For example:
=IF(ISNA(VLOOKUP(A2,$D$2:$D$4,1,FALSE)),"No","Yes")
If your business logic requires the opposite results, simply swap "Yes" and "No" to reverse the formula's logic:
=IF(ISNA(VLOOKUP(A2,$D$2:$D$4,1,FALSE)),"Yes","No")
Excel If Vlookup formula to perform different calculations
Besides displaying your own text messages, If function with Vlookup can perform different calculations based on the criteria you specify.
Taking our example further, let's calculate the commission of a specific seller (F1) depending on their effectiveness: 20% commission for those who made $200 and more, 10% for everyone else.
For this, you check if the value returned by Vlookup is greater than or equal to 200, and if it is, multiply it by 20%, otherwise by 10%:
=IF(VLOOKUP(F1,$A$2:$C$10,3,FALSE )>=200, VLOOKUP(F1,$A$2:$C$10,3,FALSE)*20%, VLOOKUP(F1,$A$2:$C$10,3,FALSE)*10%)
Where A2:A10 are seller names and C2:C10 are sales.
IF ISNA VLOOKUP to hide #N/A errors
If the VLOOKUP function cannot find a specified value, it throws an #N/A error. To catch that error and replace it with your own text, embed a Vlookup formula in the logical test of the IF function, like this:
Naturally, you can type any text you like instead of "Not found".
Supposing, you have a list of seller names in one column and sales amounts in another column. Your task is to pull a number corresponding to the name the user enters in F1. If the name is not found, display a message indicating so.
With the names in A2:A10 and amounts C2:C10, the task can be fulfilled with the following If Vlookup formula:
=IF(ISNA(VLOOKUP(F1,$A$2:$C$10,3,FALSE)), "Not found", VLOOKUP(F1,$A$2:$C$10,3,FALSE))
If the name is found, a corresponding sales amount is returned:
If the lookup value is not found, the Not found message appears instead of the #N/A error:
How this formula works
The formula's logic is very simple: you use the ISNA function to check Vlookup for #N/A errors. If an error occurs, ISNA returns TRUE, otherwise FALSE. The above values go to the logical test of the IF function, which does one of the following:
- If the logical test is TRUE (#N/A error), your message is displayed.
- If the logical test is FALSE (lookup value is found), Vlookup returns a match normally.
IFNA VLOOKUP in newer Excel versions
Beginning with Excel 2013, you can use the IFNA function instead of IF ISNA to catch and handle #N/A errors:
In our example, the formula would take the following shape:
=IFNA(VLOOKUP(F1,$A$2:$C$10,3, FALSE), "Not found")
Tip. If you'd like to trap all sorts of errors, not only #N/A, use VLOOKUP in combination with the IFERROR function. More details can be found here: IFERROR VLOOKUP in Excel.
Excel Vlookup: if not found return 0
When working with numerical values, you may want to return a zero when the lookup value is not found. To have it done, use the IF ISNA VLOOKUP formula discussed above with a little modification: instead of a text message, supply 0 in the value_if_true argument of the IF function:
In our sample table, the formula would go as follows:
=IF(ISNA(VLOOKUP(F2,$A$2:$C$10,3,FALSE)), 0, VLOOKUP(F2,$A$2:$C$10,3,FALSE))
In the recent versions of Excel 2016 and 2013, you can use the IFNA Vlookup combination again:
=IFNA(VLOOKUP(I2,$A$2:$C$10,3, FALSE), 0)
Excel Vlookup: if not found return blank cell
This is one more variation of the "Vlookup if then" statement: return nothing when the lookup value is not found. To do this, instruct your formula to return an empty string ("") instead of the #N/A error:
Below are a couple of complete formula examples:
For all Excel versions:
=IF(ISNA(VLOOKUP(F2,$A$2:$C$10,3,FALSE)), "", VLOOKUP(F2,$A$2:$C$10,3,FALSE))
For Excel 2016 and Excel 2013:
=IFNA(VLOOKUP(F2,$A$2:$C$10,3, FALSE), "")
If with Index Match - left vlookup with If condition
Experienced Excel users know that the VLOOKUP function is not the only way to do vertical lookup in Excel. The INDEX MATCH combination can also be used for this purpose and it's even more powerful and versatile. The good news is that Index Match can work together with IF in exactly the same way as Vlookup.
For example, you have order numbers in column A and seller names in column B. You are looking for a formula to pull the order number for a specific seller.
Vlookup cannot be used in this case because it cannot search from right to left. Index Match will work without a hitch as long as the lookup value is found in the lookup column. If not, a #N/A error will show up. To replace the standard error notation with your own text, nest Index Match inside IF ISNA:
=IF(ISNA(INDEX(A2:A10, MATCH(F1, $B$2:$B$10, 0))), "Not found", INDEX(A2:A10, MATCH(F1, $B$2:$B$10, 0)))
In Excel 2016 and 2016, you can use IFNA instead of IF ISNA to make the formula more compact:
=IFNA(INDEX(A2:A10, MATCH(F1, $B$2:$B$10, 0)), "Not found")
In a similar manner, you can use Index Match in other If formulas.
This is how you use Vlookup and IF statement together in Excel. To have a closer look at the formulas discussed in this tutorial, you are welcome to download our sample workbook below. I thank you for reading and hope to see you on our blog next week!
Practice workbook for download
Excel IF Vlookup - formula examples (.xlsx file)
375 comments
Hi
in BS 2 value is 1 and i am trying to use below formula.
=IF(BS2="1",(VLOOKUP(BG2,Sheet1!$F$6:$G$67,2,0)))
but its still showing " False" in cell .
Can you help me with this.
Thanks you.
Hi!
Your formula checks for the text "1". If the cell contains the number 1, then the result will be FALSE. Try to use BS2=1 in your formula. Have a look at the examples of using the IF function with text and numbers.
I have created a dropdown menu y/n. If yes, then the formula should get a value in another sheet and if no, it should just be a 0.
Yes/No - If yes, pick up value in cell x; if no = 0 - I have tried several ways. Someone that might have an idea?
Thanks.
Hello!
If your drop-down menu inserts a value in cell A1 then you can use IF function:
=IF(A1="Yes",X1,0)
Hope this is what you need.
Can you help with with what my formula format would be if I want to pull a cost from another tab if certain criteria matches in both tables?
Ie. I have a master inventory list that has serial numbers costs, etc. When I enter the sale of that serial number on another tab I want it to auto populate the cost associated with that serial entered on the inventory tab. Is that possible?
Thank you
Hello!
This can be done using the VLOOKUP function. Please have a look at this article: Excel VLOOKUP function tutorial with formula examples.
Hello, I am hoping you can steer me in the right direction. I have a spreadsheet for tracking monthly hours. It has a sheet for each month and a Summary sheet, which should show the sum of each person's hours for the year. There is a unique identifier (employee number) for each employee, so I set up a formula using SUM and VLOOKUP in the Summary sheet. I named a Range on each spreadsheet (jan, feb, mar, etc.), so it looked something like this (I am still experimenting, so the formula only goes to May):
=SUM(VLOOKUP(A2,jan,3,FALSE)+VLOOKUP(A2,feb,3,FALSE)+VLOOKUP(A2,mar,3,FALSE)+VLOOKUP(A2,apr,3,FALSE)+VLOOKUP(A2,may,3,FALSE))
The hitch: we start with a certain number of employees in the spring, and hire seasonal workers throughout the year. So the list of names in January might be 400, by April it might be 650, and by August 800. My formula works fine for the first 400 folks, but when I add more names to the spreadsheets for the following months (and extend the Ranges to include the new names), the Summary results come up as N/A for the new names. If I add an IFNA function, all new names after the first spreadsheet (jan) bring up "0" in the Summary instead of N/A:
=SUM(IFNA(VLOOKUP(A2,jan,3,FALSE)+VLOOKUP(A2,feb,3,FALSE)+VLOOKUP(A2,mar,3,FALSE)+VLOOKUP(A2,apr,3,FALSE)+VLOOKUP(A2,may,3,FALSE),0))
I am still very much an Excel novice, so any assistance would be very appreciated!
Hello!
I can't check the formula that contains unique references to your workbook worksheets. However, you are using the IFNA function incorrectly. Try to change the formula:
=SUM(IFNA(VLOOKUP(A2,jan,3,FALSE),0)+IFNA(VLOOKUP(A2,feb,3,FALSE),0)+IFNA(VLOOKUP(A2,mar,3,FALSE),0)+IFNA(VLOOKUP(A2,apr,3,FALSE),0)+IFNA(VLOOKUP(A2,may,3,FALSE),0))
Also, a pivot table is perfect for your tasks. Please have a look at this article: How to use pivot tables in Excel - tutorial for beginners.
Thank you very much for your help - I am off to learn about Pivot Tables!
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
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 200 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
Hi!
You are repeating a question that was already answered yesterday. What formula are you talking about? Maybe this article will be helpful to you: How to Vlookup in rows and columns (two-way lookup).
I have a dataset where a single person will have several records that look very similar except for the date column.
So if BRAD has 8 apples as of Jan 1 (row 1) and 10 apples as of March 10 (row 2) and 4 apples as of April 4 (row 3), I would want to return a value of 4 because it is the most recent date.
BRAD - APPLES - 1/1/2022 - 8
BRAD - APPLES - 3/10/2022 - 10
BRAD - APPLES - 4/4/2022 - 4
Is there a way to accomplish this with VLOOKUP and IF functions?
Hello!
Search using INDEX+MATCH. You can find the examples and detailed instructions here: Excel INDEX MATCH with multiple criteria.
=INDEX(D1:D10,MATCH(TRUE,(C1:C10)=MAX((A1:A10="Brad")*(B1:B10="apples")*(C1:C10)),0))
Hope this is what you need.
Hello! may ask something, about vlook up, how can I automatically add new text in the table, then automatically apply in the advanced filter?
Im creating a gsheet for prorated leave balance, if employee reach 2 yrs in service she/he will have 14 leave credits, if reached 3, 4, or 5 yrs will have 15 leave credits.
But if employee reached 1 year, it will be prorated. For example her one year fall on January 1 to 15, automatically she will have 12 leave credits but if her one year fall on January 16-feb 15, she will only have 11 credits, if one year fall on Feb 16-march15, she will have 10 credits and so on. How can I formulate that?
Hello!
Count the number of months using the MONTH function -
=12-MONTH(A1)+(DAY(A1)<16)
I hope it’ll be helpful.
A Cell - B Cell the answer is C cell. if B cell 0 Mean the Answer C Cell should get value for A Cell.
My question is If B Cell 0 Value mean should C Cell also need get value 0.
therefore, what is the function i need put in C Cell.
please explain me
Hi!
I’m not sure I got you right since the description you provided is not entirely clear. However, it seems to me that the formula with IF function below will work for you:
=IF(B1=0,0,A1-B1)
I m working with two sheet in sheet 1 having primary column and in sheet 2 having primary column and sub column ..i compared two sheet and trying to get the match records . My requirement is need to get the subcolumn match name in sheet 1 note=Column names are string
for ex -I tired this IF(Vlookup(Sheet1A2,sheet2A2:B210000,2,false)="Jan","yes","no")
But not getting the correct result. Any solution?
Hello!
Here is the article that may be helpful to you: Compare two columns in different Excel sheets using VLOOKUP.
=IFNA(VLOOKUP(A2, Sheet2!$A$2:$B$1000, 2, FALSE), "")
I hope it’ll be helpful.
Hi!
I'm wanting to first check a column to see if a person passes a level of cognitive ability before using VLookUp to determine their level of fit with the job. Specifically, if in column A it says "not fit" (meaning they didn't pass), I don't want to look at the rest of their scores (in another column) to determine their overall level of fit. How can I accomplish this? I feel like I need an If statement somewhere, but I don't know how to combine these together.
Hello!
Check the condition in cell A1 using the IF function:
IF(A1="not fit","",VLOOKUP( ........))
Hope this is what you need.
Ive been following this thread with interest to find a solution to using vlookup based on a cell reference.
Im trying to perform one of 3 vlookups within the same cell based on the value in a reference cell (ie: cell B3 contains either 1,2 or 3 which relates to data in a different sheet in the workbook) I then want to vlookup cell A3 in the corresponding sheet and return a value.
My logic is =if B3=1 then vlookup A3 in sheet1 range A1:C100 and give me the value in column C else if B3=2 then lookup A3 in sheet2 same range and cell as before else if B3=3 then lookup A3 in sheet3 etc.
Im struggling with correct format to perform the above so any tips are greatly appreciated.
Hello!
To create a dynamic link in a VLOOKUP formula, you can use the INDIRECT function. I recommend reading this guide: INDIRECT formula to dynamically refer to another worksheet.
You can also use the nested IF function.
=IF(B3=1, VLOOKUP(A3,Sheet1!A1:C10,2,FALSE), IF(B3=2, VLOOKUP(A3,Sheet2!A1:C10,2,FALSE), IF(B3=3, VLOOKUP(A3,Sheet3!A1:C10,2,FALSE),"")))
This should solve your task.
Hello team,
Currently I am working on a table in which I have to do the following:
1st Check if specific cell A1has writen "OK" and if it is true then return "4", If not then lookup another cell's value B1 and return a value from array C2:F8 corresponding to the previous cell B2.
The tricky part is that in the array from the lookup there are blanks which must be bypassed. If C3 is blank then use C4, if C4 is blank then use C5 etc.
=IFERROR(IF(A1="OK",4,VLOOKUP(B1,C2:F8,??????)),"ERROR")
I am unsure how to implement ISBLANK or if there is another solution
Hello!
And your explanation is not entirely clear what "corresponding to the previous cell B2" means. In addition, empty cells in the search column do not affect the result. Therefore, I don't really understand your problem. Maybe the VLOOKUP formula should be like this:
=VLOOKUP(B1,C2:F8,B2,FALSE)
If this is not what you wanted, please describe the problem in more detail.
helped me a lot, thanks
I want to make lookup and average at the same time.
e.g.,
"Sheet A" has data and time in one cell and i want to find that date and time exactly in "Sheet B".
Once i find in "Sheet B" the exact date and time in next column i want to have average of last 15 values or cells. And this all i need to see in sheet one with 15 cell average.
Please assist urgent.
Hi!
Unfortunately, your task cannot be solved using single Excel formula. I can't see your data, but I think you should use a VBA macro.
Hello Team :)
I am trying to to use the IF function with a lookup table, using a value of 1 for males, 2 for females, to search and return the correct result depending on which gender applies.
My current, formula is:
=IF(C2=1,VLOOKUP(C2,B25:E30,2),VLOOKUP(C2,B25:E30,3))*1000
The formulas for each age group are in two colums, column 3 for males, column 4 for females.
I can't get the formula to choose the right column as designated by the 1 or 2 choice.
I'm not sure if that is sufficient information.
Additional information:
Using Schofield's formula to calculate daily (basal) energy needs
SCHOFIELD BMR EQUATION - MEGAJOULES
AGE GROUP WEIGHT MEN WOMEN
10 - 18 83 8.896 7.546
19 - 30 83 8.125 7.182
31 -60 83 7.637 6.360
> 60 83 6.526 5.909
A person over 60 weighing 83kg needs 6.526 mj (males) or 5.909 mJ (females).
I just need to lookup to to choose according to the 1 or 2 designated - 1 will return 6.526, 2 will retun 5.909.
Thanks!
Hello!
If I understand your task correctly, the following formula should work for you:
=IF(C2=1,VLOOKUP(C1,B25:E28,3,TRUE),VLOOKUP(C1,B25:E28,4,TRUE))
C2 is 1 or 2
C1 - this is the age in years.
B25:B28 is 10, 19, 31, 60
Read about VLOOKUP approximate match (TRUE) in this article.
This should solve your task.
can u help me with this scenario
final numerical
performance rating
8.38 <-------------RATING BUT IT DISPLAY (VS)
equivalent adjectival rating
= (VS) <------ I WANT TO SHOW SOMETHING LIKETHIS
THE DATA BELOW RANGE FROM
9.5-10 = OUTSTANDING (O)
7.51-9.49= VERY SATISFACTORY (VS)
THANKS
Hello!
Use the guidelines and examples from this article - Excel IF statement with multiple AND/OR conditions, nested IF formulas
=IF(AND(A1<10,A1>=9.5),"O",IF(AND(A1>=7.5,A1<9.5),"VS",""))
Hi,
Have a peculiar query.
The following table denotes achieve numbers in the unit tests.
Name Unit 1 Unit 2
A 100
B 95 98
C 85
D 92 96
E 65 85
F 99
G 85 92
Whosoever have taken UNIT 2 test. That number will be considered, else UNIT 1 number.
Pls help with formula
Hello!
If I understand your task correctly, the following formula should work for you:
=IF(C2="",B2,C2)
I have the almost the same data with example number 1 but instead of it being zero (0), the column is blank (no data). How do I replace the (0) in the formula if the data in the column is empty?
=IF(VLOOKUP(E1,$A$2:$B$10,2,FALSE)=0(what if the value of column is empty/no data?),"No","Yes")
Hello!
This formula works if there is a blank cell instead of 0. You don't need to change anything in the formula.
Hi,
I need help with a formula. I need to do a Vlookup or formula based on a location I type in the cell to reference that locations data. If I type a location in a cell (ex. Atlanta) I need to do a Vlookup based on location 1's (Atlanta's) data. If I enter location 2 (ex. Charlotte) in the cell I need to reference location 2's (Charlotte's) data.
Thank you,
Hello!
Unfortunately, without seeing your data it is difficult to give you any advice. If you enter location data in cell A2, then in the formula in cell B2 you must use the data from A2. You can see examples of VLOOKUP in this article.
If this is not what you wanted, please describe the problem in more detail.
Correct me if I'm wrong
based on my understanding @hollie has 2 different set of data which are known as Atlanta and Charlotte as separate table and @hollie want to use those data set as vlookup point of refference for return value
I'm still learning and advice my solution since I just get the undertanding of the formula 2 minutes ago
Hollie need to combine Charlotte and Atlanta Table as one big table and ensure to use unique data as first column also using those unique value as data entry for serching return value