*The tutorial shows how to combine V**LOOKUP 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:

*sample_value*, TRUE, FALSE)

Translated in plain English, the formula instructs Excel to return *True* if Vlookup is true (i.e. equal to the sample value). If Vlookup is false (not equal to the sample value), the formula returns *False*.

Below you will a find a few real-life uses of this IF Vlookup formula.

#### Example 1. Compare Vlookup result with 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 instead of IF Vlookup

In Excel 2016 and 2013, you can use the IFNA function instead of IF ISNA catch and handle #N/A errors:

*Not found*")

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.

## 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 to Excel If Vlookup. I thank you for reading and hope to see you on our blog next week!

Doubt in google Excel sheet Formula:

I created two sheets. sheet one contains full data base with date and name , In sheet2 I have form submission lead , That is date and name with other data's. Here i need to make display other data in sheet 2 related to the particular Date and name from sheet 1.

Hello,

For us to understand your task better, we need to look at your data. If it is possible, please share your workbook with the source data and the expected result with support@ablebits.com. Please shorten the tables to 10-20 rows/columns and do not forget to include the link to your blog comment in the email.

We’ll look into your task and try to help.

Hi Svetlana,

Love your Excel tutorials.

I have a problem.

I have two spreadsheets. Spreadsheet "A" has a list of part numbers and sell prices. Spreadsheet "B" has the same part numbers plus more which spreadsheet "A"does not have - including prices.

When I populate prices from SSHeet "A" to SSheet "B" using VLookup, where there is no data from Ssheet "A", I naturally return a #N/A. So what I want to do is somehow create a Vlookup formula where I can also include a formula which will use another cell in SSheet "B" where I have a cost of product and apply a margin to that cost and do this all within the VLOOKUP formula. Is this possible?

Thanks Svetlana.

Love your tutorials

Hi Nicholas,

Thank you for your kind words!

I do not exactly understand the part where you apply a margin to the cost, but I believe the general idea is feasible. You coulod nest IFERROR within VLOOKUP as shown in this example.

Hi , I saw your solutions and im impressed, maybe you can help me as well ..

I have question :-)

I use =IF(I3="SE",VLOOKUP(B24,ls_all_courses,7,0)) to find a numeric value (Column 7)

But i want to add more choices with IF , IF(I3="XX",VLOOKUP(B24,ls_all_courses,8,0

So if it is XX instead of SE , the column should be 8

Is this possible or do i need to use another formula ?

Thanks in advance

Kjell

Hello Svetlana,

I have calculated a 'x' value in a cell by using formula.

Now I have to use that 'x' value in vlookup by giving cell address but it is showing error #NA.

Could you please help me out. I want vlookup to use the value from a particular cell address which is calculated by a formula to show desired result.

Please help....

-Regards,

Shrikant

Hi Shrikant,

Normally, you simply supply a cell reference instead of the lookup value in the first argument, e.g. =VLOOKUP(D1,A1:C10,3,FALSE)

It's hard to say why you are getting the #N/A error without seeing your data. Can you post both of your formulas here, the one that calculates "x" and Vlookup?

Hi,

Thanks for the help, is it possible to, when the vlookup finds a match it then displays the data in another column on the same row as the matched data? essentially I have a two lists of orders and have compared the two to find orders that have doubled up.

What I used was

=IF(ISNA(VLOOKUP([@ID],'Sheet2'!E:E,5,FALSE)),"","Found")

At them moment I have to then find the entry and manually find the dates to see when the double up occurred, if i could automate that it would save me a bunch of time.

-Thanks,

Stuart

=IFERROR(VLOOKUP($G$14&$K$14&$O$14&$R$14&$D19&$E19,'RAW DATA'!$A$1:$AB$8000,F$16,FALSE),"")

=IF($K$14="NUMERIC DISTRIBUTION %",TEXT(F19/100,"0.00%"),F19)

i want to merge both formula in cell F19. can it possible...??

In second formula if i select another option replacement of "NUMERIC DISTRIBUTION %" (K14 is validation list) than run only vlookup formula.

if with vlookup for Value based or % not Text So. 1 to 1000 = 5%, 1001 to 2000 = 15%, 2001 to 5000 = 18%. 5001 and above 20% Interest or Tax like calculations required

How is it possible to use vlookup for drop down and also insert if formula in the drop down

Why my VLOOKUP does not show the result, though I can see the formular,when I click in the cell

Hello everyone,

Can someone help me with a formula (not sure if its even possible). I'm trying to create a tracker of lets say i choose an option from a drop down menu in E9, i want all the Cells From F9 to N9 to automatically populate(I figured how to do this already), but what i'm really trying to figure out is if there is a way to have cell F9 (if blank) to insert a date 1 year before a date in cell C9. Hope i'm making sense.

Thank you.

Hi,

We are a small manufacturing company in Texas. I verify material receiving dates against PO due dates with the falling formula. A spreadsheet for receiving and one for purchase orders. The problem is if the due date field in the PO spreadsheet doesn't have a date, then it comes back with a "N". Making it appear that we did not receive it in time. I was wanting to only target the dates in the PO master (due dates) that have a date in the cell. I've been trying to get everyone to put in an appropriate due date, but not everyone wants to get on board. Any help would be greatly appreciated.

Thank you,

Mike

=IF(A3977<=VLOOKUP(C3977,'[Purchase Order Master.xls]Sheet1'!$A:$J,8,0),"Y","N")

Is there a way to choose which data is displayed with the IF formula based on the text in a certain column? Example: If column A is red it will display the information from column B, but if column A is any other color it will display information from column C?

I want a multiple argument in my vlookup as follows:

Do it on the Required Field (as the only field) and the logic is:

1. If the vlookup finds a match, then return the value in the range

ELSE

2. If it does not find a match but the values of 2 separate fields are X1 = ABC and xx1 = DEF & XYZ, then return R

3. In all other cases where it does not find a match, then return NR

I already have the vlookup to return the value else NR - see below:

=IFNA(VLOOKUP(B9,H$1:I$2112,2,FALSE),"NR")

A IF the value is Equel to or above then 85 the payout shoud be 500

b IF the value is Equel to or above then 90 the payout shoud be 800

C IF the value is Below then 850 the payout shoud be 0

Please show me how to fix it by using IF.??

Hello -

Need help. Need to do vlookup for spreadsheet containing countries and accounts codes. How can i match the account code in each countries. I need to get the correct mapping of Final account for France and same with other countries.

Sample:Raw Data

Country Account Code

France 50152

Spain 50152

Italy 50152

Master File:

Country Account Code Final Account

France 50152 50155

Spain 50152 50140

Italy 50152 50150