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, can someone please try to help me.. So I got 2 criteria.
I am wanting to automate invoice dates based on criteria. Delivery and Activation date.
I already created helper column for the delivery and activation dates. Now I just want to formulate and not look manually. So I got items that needs to have invoice date +1yr from the payment date if they have no invoice or activation date.. Can someone please assist me?
Payment Date Column A
Invoice Number Column B
Activation Helper Column Q
Delivery Date Helper Column R
Invoice Date Ref Column P
So on Column P,I got delivery and activation date, if invoice was Activation date,then there is activation code on Column Q, invoice date should be payment Date, if the Activation Helper Says No, invoice date should be +1yr from payment date.
Same with the delivery date, if there is a delivery date on Column R, the invoice date should be delivery date,if not delivery date,should be +1yr from payment date
Hi! From your explanation, it is not quite clear what is written in column P - Invoice Date Ref or delivery and activation date.
Use the IF formula to set the value of a cell based on a condition. For example:
=IF(Q2="yes",P2,EDATE(P2,12))
To add 1 year to the date, you can use these instructions: Excel EDATE function to add or subtract months from date.
You can also use these recommendations: Add years to date in Excel.
Hi,
Do you know how can I create a formula with multiple criteria but with same result? i.e.: I have "John, Martha, Kevin, Robert", but I want to show that only "John and Kevin" are "Active users", no comments for the other two.
Hello Guilhermo!
Unfortunately, this information is not enough to recommend a formula to you. It's unknown what your definition of active users is, what your source data is, and what the expected result is.
I have a monthly time sheet for a single staff member in South Africa. Her hours are flexible and that makes the tax rate differ monthly.
Once I have her monthly figure from the hours worked I need to refer to a table provided by SARS (tax) and if her earnings are between X and Y then I choose that value.
How do I set up my excel to automatically access the table (I have retyped the rows/columns that would be relevant (base remuneration, top remuneration, tax rate). So for example:
If she earned R9287 in the month I would look at the table row where the salary falls between R9222 and R9322 and select the tax figure of R233.
I would like the system to do this for me. HELP.
Thanks.
Hello Janet!
To check that a number is within a certain interval, use approximate match. We have special tutorials on this. Please see: How to Vlookup for approximate match and Approximate match XLOOKUP.
The data you have provided is not enough to offer you a formula.
Thank you Alexander. I will give it a go and come back to you with more details if I get stuck again.
I want to check if there is a formula for returning value as 0 or blank, if the value has already been looked up.
For example, lookup value is in Column A, some are duplicated, some are not. So if I want to look up the value of "123x", I do the formula on Row1 ColB. But for Row 2, I want it to return value as zero, since its already been looked up in Row 1. Can I do that? Thanks in advance.
Col A
Row 1 123x
Row 2 123x
Row 3 123x
Row 4 124B
Hello Juli!
I can't guess what you want to get if the value you are looking for is found. To find only the first match, you can use the COUNTIF function.
=AND(COUNTIF($A$1:$A1,"123x")=1,A1="123x")
This formula will return TRUE. Copy this formula down along the column.
I understand the scenario I also need help in this situation, basically if the first lookup value has been found the other lookup value with same text or number will just result to zero
Hello Paul!
If I understand your question correctly, to get 1 or 0 instead of TRUE or FALSE, use a math operation. Read more: Change Excel string to number with mathematic operations. For example:
=--AND(COUNTIF($A$1:$A1,"123x")=1,A1="123x")
You can also find useful information in this article: How to highlight duplicate cells and rows in Excel. The formula might look like this:
=--(COUNTIF($A$1:$A1,A1)<=1)
Hi - I want to do a if vlookup that if true the cell will populate with text from another sheet. example. If these people signed up for math class, populate the cell with the name of the person who signed up. so check the column for yes math class then add the name of the person in the cell. Thanks!!
Hi! Information you provided is not enough to understand your case. Try using recommendations from the article above. You may also find this guide useful: Vlookup from another sheet. If this does not solve the problem, try explaining the problem in more detail.
Hi. Ok let me try again :)
Sheet 1 has a list of people signed up for multiple classes, Friday at 4-5pm, Friday 5-6pm, Monday 5:30-7pm, etc. I want to look up who is signed up for Friday at 4-5pm class. If the vlookup is true and a person signed up I want the persons' name to be displayed in the cell of the if vlookup formula instead of just "yes". Hopes this makes sense! Thanks
Hi! I can't recommend a formula for you as I can't see example your data. However, all the necessary recommendations to find a person's name by day of the week and time are in this article: Excel INDEX MATCH with multiple criteria - formula examples.
It will be much easier to find person's name that you need if the date and the time are written in different columns.
Hello ,
Would seek help, desperate trys but failed from past 3 months, Case is in a excel there are 40 sheets , First sheet has categories , and Subset of catergory has 5 sheets for each catergory,
i want to lookup if Value in Colum A is alphabet E then it should fist lookup for E in category sheet and then if Colum B is L as cell value then it should lookup in subset of Colum A (E is the value) , if first colum A has value of E then it should first search for e in catergory sheet and return the value, and then second cell colum B should be searched in Subset sheets of catergory sheet , please help !
Hello!
Your request goes beyond the advice we provide on this blog. This is a complex solution that cannot be found with a single formula. To create dynamic references to worksheets, ranges, or cells, you can use the INDIRECT function as described in this guide: How to use INDIRECT function in Excel - formula examples. If you have a specific question about the operation of a function or formula, I will try to answer it.