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
I have data sheet with id status and I working same workbook another sheet 2. while enter the data id status is terminate want to highlight automaticaly
I have a situation when I have students who have taken a test, and if they got 70 or above, I want it to say "HIS." However, there are many students who did not take the test at all or did not get 70 or above and in both of these instances, I want it to return blank.
Currently, I have =IF(VLOOKUP(A:A,'co 2024 (2)'!A:AC,29,false)>=70,"HIS",""). However, the blanks on the "Co 2024" tab are still coming up as HIS, even though I ideally would like them to come up as blanks. Can anyone help me out here?
Use simple conditional formatting a simple condition make false disappear make condition if there is false then text color is white.
Hi..
I have the formula as IF(P3="CSCL_REV","=vlookup(E3,'[Profit_&_Loss_Detail_Report AFKO.xlsx]AFKO'!$B:$E,4,0",0). What I want to get here is if P3="CSCL_REV", a value from a vlookup function. But this is not working & returning the same vlookup function if it is true. Thanks in advance.
I am trying to make this comparison check work in Excel 2016
=IFNA(INDEX('Redundancy Details'!D6:D7, MATCH('Redundancy Details'!K6:K7, 'Changing Roles'!E2:E3, 0)), "Not found")
I search across several tabs and I compare D6 with K6 and if they are the same then I want to show the value of D6 however I receive the #VALUE! error indicating that a value used in the formula is of a wrong data type, I tried to change values from text to numbers but have the same result any suggestions?
A B C E F G
a MDF 15 10 a MDF 15
b MDF 10 5 b MDF9
kindly who used vlookup if Clom A and Clom E consider match and B and F apply vlookup
Can somebody help me to find a formula for the items ("A,B,C") of column a .e.g,
if the same items in column a have dates in column b for all the respective cells, should give result complete otherwise incomplete.
Column (a) Column (b) Result Column (c)
A 12-Apr Completed
A 13-Apr Completed
B 13-Apr Incompleted
B Incompleted
C Incompleted
C Incompleted
C Incompleted
C Incompleted
I have 2 IF formulas that I need to perform on 1 cell to determine the outcome, how do I combine them into one so if the first one applies then do this but if it doesn't then do the second? These are the 2 formulas that work independently
(IF(VLOOKUP(B2,'AT Import'!C:H,6,false)="NP",(VLOOKUP(('Client allocation'!B2,'AT Import'!C:D,2,FALSE)-365),(VLOOKUP('Client allocation'!B2,'AT Import'!C:D,2,FALSE)))
IF(VLOOKUP(B2,'AT Import'!C:I,7,FALSE)="ok",(VLOOKUP('Client allocation'!B2,'AT Import'!C:D,2,FALSE)+365),(VLOOKUP('Client allocation'!B2,'AT Import'!C:D,2,FALSE)))
It's driving me nuts thanks!
Hello Team,
I have attendance data of 4 years. i want to see the summary of one person, so i am looking any formula which can take the desire value from that sheet.
I want an if statment Vlookup that instead of replacing the N/A it simply wouldn't change it at all.
For example I have pending, declined, approved.
I want a Vlookup to change from Pending to Approved or Declined but sometimes we update the rest on the second day. So I would want which wasn't found to be as it is and the Vlookup doesn't change it. Is that possible?
Hi,
I am using a VLOOKUP function to find a value form a different tab but I need to replace what it finds with a different description i.e. When it finds "Steel Drum" I need that to be replaced with "Type 2 Drum".
Any help would be appreciated.
Many thanks
Ed
Hi. Try
=IF(VLOOKUP(D20,R:S,2,FALSE)="steel drum",("type 2 drum"),(""))
Just replace
D20 with the value you're looking for
R:S the 2 columns where you're looking from
the end "" if you want it to say anything else
i want use VLOOKUP between tow dates like i have January month data but from there need to pick only 15 January to 25 January data as per dates...
1000 KA 3% =30 (MAXIMUM 25 YA MINIMUM 3%)
Hi
I have two sheets one is for staff another for day request
In the first sheet for "staff" column for 'Leave Type'such as sick leave or vacation leave, and another 'start date'the date of starting of the leave
In the second sheet, there is a cell "Date of presenting the leave"
How I can in the cell of Date of presenting the leave, write formula to calculate the following ;
if the type of the leave is vacation leave the date will be before 3 days from the date of this leave which existing in column of 'start date'for this leave
and if the type of the leave is Sick leave the date will be after 3 days from the date of this leave which existing in column of 'start date'for this leave
looking to create a spreadsheet with some complexity with user input and data supplied.
Willing to pay for its creation, but need to discuss with someone for details? what is the best forum to find someone?
How to combine this 2 formula?
Need help.
=IF(LEFT(E2,1)="B","YES",IF(LEFT(E2,1)="W","YES","NO"))
and
=IF(L2="B999","Y",IF(L2="W999","Y",IF(L2="DB10","Y",IF(L2="RC10","Y",IF(L2="TTST","Y",IF(L2="UTST","Y",IF(L2="USTS","Y","N")))))))
Hi All, Please kindly help me check if the below formula using if and vlookup is wrong?
=IF(VLOOKUP(A5,A2:A4,1,FALSE)=0,"No","Yes")
I got the outcome of the vlookup value as yes which is correct but for value that were not found, I`m getting as #N/A instead of the suppose return value of "No"
Hi I need a how to applyy comdition& formulass for one equation /one bracaket)
I have origin as mumbai and destination as a ahmedabad and
Origin Dst Destination Base Fuel Distance
Ahmedabad BTH Bangalore 78.11 1518
Hello
I am trying to compare vlookup values in order to insert up or down arrows or equal sign based on these conditions. I have used the following formula but it doesnt work:
=IF(VLOOKUP($C$2,'INTL FOB-CFR FOREX'!$A$1:$SO$965,7,TRUE)>VLOOKUP($C$2-7,'INTL FOB-CFR FOREX'!$A$1:$SO$965,7,TRUE),"▲"),IF(VLOOKUP($C$2,'INTL FOB-CFR FOREX'!$A$1:$SO$965,7,TRUE)<VLOOKUP($C$2-7,'INTL FOB-CFR FOREX'!$A$1:$SO$965,7,TRUE),"▼"),IF(VLOOKUP($C$2,'INTL FOB-CFR FOREX'!$A$1:$SO$965,7,TRUE)=VLOOKUP($C$2-7,'INTL FOB-CFR FOREX'!$A$1:$SO$965,7,TRUE),"=")
Many thanks for your help.
Best regards,
Magnus
Hello, Magnus,
I'm sorry, it's rather difficult to correct your formula since it misses a few important parts:
1) The first argument of the Vlookup function cannot be a range ($C$2-7). It should be either a value or a single cell reference. Also, the correct data range should be written like $C$2:$C$7.
You can learn the basics of Vlookup here.
2) Also, you entered excess closing brackets after each "▼" that break you nested IF. Please refer to this article to check what arguments IF and nested IF contain.
3) The very last part of the formula misses the third argument after "=" that would indicate what to return if none condition is met.
Please consider these points when fixing the formula.
Dear Natalie,
Many thanks! I have removed the brackets and the formula now works! The '$C$2' is a weekly date reference and the '$C$2-7' to get the previous week value.
The formula now reads as follow:
=IF(VLOOKUP($C$2,'INTL FOB-CFR FOREX'!$A$1:$SO$965,31,TRUE)>VLOOKUP($C$2-7,'INTL FOB-CFR FOREX'!$A$1:$SO$965,31,TRUE),"▲",IF(VLOOKUP($C$2,'INTL FOB-CFR FOREX'!$A$1:$SO$965,31,TRUE)<VLOOKUP($C$2-7,'INTL FOB-CFR FOREX'!$A$1:$SO$965,31,TRUE),"▼",IF(VLOOKUP($C$2,'INTL FOB-CFR FOREX'!$A$1:$SO$965,31,TRUE)=VLOOKUP($C$2-7,'INTL FOB-CFR FOREX'!$A$1:$SO$965,31,TRUE),"=")))
Thank you again.
Best regards,
Magnus Berge
You're most welcome, Magnus,
Now I've got those parts of your formula as well, thanks for the explanation!
Anyway, glad to know my suggestion worked! :)
Thank you again, Natalia.
Happy New Year!
Best,
Magnus
Happy New Year, Magnus! :)