VLOOKUP with IF statement in Excel

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:

IF(VLOOKUP(…) = value, TRUE, FALSE)

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. 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")

If Vlookup formula to return Yes or No based on vlookup result

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:
If formula with Vlookup to compare vlookup's result with another cell

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( ISNA( VLOOKUP(…)),"No","Yes")

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")

Vlookup values in a shorter list and return Yes or No.

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")

IF ISNA VLOOKUP formula to look up values in a shorter list and return Yes or 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.
Excel If Vlookup formula to perform different calculations

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:

IF(ISNA(VLOOKUP(…)), "Not found", VLOOKUP(…))

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 ISNA VLOOKUP formula pulls a matching value

If the lookup value is not found, the Not found message appears instead of the #N/A error:
If the lookup value is not found, IF ISNA VLOOKUP returns custom text 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

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

IFNA(VLOOKUP(…), "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. 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:

IF(ISNA(VLOOKUP(…)), 0, VLOOKUP(…))

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))

If Vlookup formula: if not found return 0

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:

IF(ISNA(VLOOKUP(…)), "", VLOOKUP(…))

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 Vlookup formula: if not found return blank (empty string)

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")

Using If with Index Match to do left lookup without N/A errors

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!

You may also be interested in

182 responses to "VLOOKUP with IF statement in Excel"

  1. Tina Blalock says:

    Data on file i am bouncing to has a Y or null, and then there is the possibility of #n/a error. i'd like the result of my vlookup as follows:
    if Y, then Yes
    if null, then No
    if not found (#N/A), then null

  2. Chris says:

    I want to have an if function which returns the value of the next cell if the value is zero

  3. Silviu says:

    Hello Alexander ,

    Thank you Alexander for help !

    I fond one new formula on the internet and I think is more simple to use for my scope:

    =LEFT(A1;18)=''01234-23456-234556''
    where A1 is the cell with ''01234-23456-234556-ABCD'' and ''18'' is the number of caracters counted from LEFT SIDE

    • Hello!
      You didn't say anything about the fact that the number of characters in your text is always the same. My formula works with any number of characters. Your formula only works when the number of characters is 18.
      It is a pity that you did not mention this. I would have spent much less time answering.

  4. Jane says:

    Hi! I hope you can help me on this situation. I need a formula that can part our sales.

    Scenario:
    Sales is $100 . This must be automatically parted to 80% and 20% in a particular sales territory.

  5. Paul says:

    So I am have question and I am not sure if it can be done with excel.
    I have 3 columns, I am comparing the data from columns A and B using VLOOKUP
    to express the results on column C, I get the result of N/A for the values missing from
    A and B. Is there a way instead of showing 'N/A' to show the values from column A
    so I know exactly which values are not present in Column B? I don't want to see 'No ,Yes, True or False'

  6. Huub Aerts says:

    Hi,

    I have an other problem (I think).

    I do a VLookup. But if the cell of the requested Output is empty, I want to get the next value (lookup the next row in the range that equals the Lookup match.

  7. Haley says:

    I am looking for a formulas that I am guessing is some sort of IF and VLOOKUP combination.
    I have a Main excel sheet that will hold all "donations" made: Name, contact info and a column of each department a donation was made to.
    Each Department has it's own column and its own sheets, so I would like Names and contact Infor & amount donated shared with that departments sheet, if there is an amount in the main sheets column.
    So IF Column4 has $ammount in Row1, Then Name, address, phone #, email, and $amount in Row1 is shared with the corresponding Sheet for Column4.

    • Hello!
      Unfortunately, without seeing your data it is impossible to give you advice.
      I'm sorry, it is not very clear what result you want to get. Could you please describe your task in more detail and send us a small sample workbook with the source data and expected result to support@ablebits.com? Please shorten your tables to 10-20 rows/columns and include the link to your blog comment.
      We'll look into your task and try to help.

  8. Jo says:

    I have a worksheet with two tabs. My first tab (A) is my results page and I usually use a vlookup to match identifiers from Tab A to Tab B that then finds associated data for the identifier. (eg: (VLOOKUP(B4,'INFO_201801-201812'!G:H,2,FALSE) That is usually fine, as the data I need is always in a specific place on my Tab B. However, I have found an issue with my original data and the identifer in Tab B can be found in one of two different columns. (eg: F:H or G:H) Can i combined two vlookup formulas into one IF statement so that, if the data I need is not found in the first column (F) on Tab B it will check the second column (G)?

  9. Amit says:

    Please help here to find a match value, which formula should I use to get it. Example below.

    In Column A I have a value and on that basis I need to find a match with reference to the Multiple column like, if you find a match for column A from column B give me a value if not find in Column B look in column C and if not column C look in column D and give me a value.

    Which formula should I use here. Thanks.

  10. Ariel says:

    Hoping for your best formula by understanding what I want the outcome is.

    My situation is like this. In cell H4 and I4 is the Basic Pay on an employee separated whether the basic is MWE or not, so only one cell will only have the data needed. In cell AD4 is the EE share for PHIC here in Philippines. In cell AR4 should be the ER share (this is where I want my formula to work on).

    In another sheet 'Formula Source'!B2:E5 is my table range where my vlookup should return the value in index 3 (or in any value in Cell D2 to D5). But the value in D2 is a fix amount of P300 and in D3:D5 is only a percentage rate given with a compensation range in B2:B5.

    My formula goes like this: =IF(OR(SUM($H4:$I4)>'Formula Source'!$B$3,$AD4>0),(SUM($H4:$I4)*VLOOKUP(SUM($H4:$I4),'Formula Source'!$B$3:$E$5,3,TRUE)),VLOOKUP(SUM($H4:$I4),'Formula Source'!$B$2:$E$2,3,TRUE))

    Well, this gave me a result but even those who are zeros in AD4 (column AD) still have values where this should also be zero.

    Hoping for your better analysis. Thank you.

  11. Daniel says:

    I'm trying to match information from two sheets (First Name, Last Name & DOB), but also need the formula to pull data from 3 separate columns if a match is found (Date, Action, Customer ID).

    Sheet 1 only has First Name, Last Name & DOB.
    Sheet 2 has First Name, Last Name & DOB + a specific date, an action (Add or Delete) and a customer ID.

    Can this be done with IF+VLOOKUP?

    Thanks!

  12. Jomel duka says:

    is there a way to show vlookup result in series? vlookup always show first value
    for example:
    order number 1 has to 2 order item1 apple item 2 is orange item3 is banana

    order # | item
    1 apple
    1 apple
    1 apple

    instead of:
    order # | item
    1 apple
    1 orange
    1 banana

    please help me on this
    thanks!

  13. Manish says:

    I have a data in which The team mapping from 1st-24th was different and then from 25th the team mapping has changed how can I use the If with Vlookup

  14. Patrick says:

    I have two sheets. In the first sheet I miss a date, which can be found in the second sheet. The rows (in the second sheet) in which these dates can be found is also a unique ID. These ID numbers are also present in the rows in the first sheet which requires the dates.
    I would like to define a formula that takes the dates from the second sheet and prints it in the right cell in the first sheet. The date is placed correct if the ID in the first and second sheet match.

    How can I use 'IF' and 'VLOOKUP' to define such a formula?

  15. Kristian Kirkegård Mortensen says:

    Hi :)
    I have a unbalanced panel data set I want to sort.

    How can I code excel to look in a column match with a condition, and then take the sum of the values which also meets the same condition?

    The goal is to get the mean of my parameter for each country for each year in the panel data set. So I only have the parameter in years for each country.

    I was thinking that some of the functions here could be usefull, but I cant quite figure out how to combine them to get the wanted result.

  16. Nazir McDonald says:

    good day, I want to extract information out of an excel table for every customer that needs a refund. i made a column that states "REFUND DUE" and it says "yes" or "no" for when refund is or isnt due. i need to extract all the "yes" to another table. what formula can i use to do so?

  17. Belinda A Ndam says:

    Hello,

    I am trying to lookup 3 criteria's from a range of data on a different sheet, and match to 3 criteria's on my current sheet and if they all match say "Yes", if Not say "No".

    Can you please help me with that.

    Thanks

  18. Kathleen says:

    Hey,

    I have following issue.

    for example my If should be: if the content of cel from kolom A on this row occurs in kolom B then it should say "Slave" else it should say "Master"

    We used this formule: =IF(VLOOKUP([@[xxx]]=@xxx_HP;1;FALSE)=TRUE;"Master";"Slave")
    But we get a #N/A

    Thanks

  19. Aaron J Heck says:

    trying to write a vlookup with where if is a certain cell i would like it to multiply to the power of 10 and than divide by 2 and if it is not the certain cell than to just multiply by power of 10 here is what i got so far.

    =IF(OR(E2="",$D2=""),"",SUM(IF($C2="Buy",(E2-D2),(D2-E2)) * POWER(10, VLOOKUP($L2,'Pairs time power of 10'!$A:$C,3,FALSE)),0))

    I need to add this

    IF(b2 is 'Pairs time power of 10'!B37,'Trading Journal'!, divide by 2

    I hope i explained what im looking for correctly. the equation i have already is perfect for my other pairs for forex, but a few are stubborn and need to be divided by 2, so i am trying to figure it into the equation

  20. abdelhamid antar says:

    i need function calculate variable discount for different products
    with if and & vlookup

  21. Saikat Dutt says:

    I am comparing two cells (contains nos.) which have Vlookup formula in it by simple IF formula.
    My logical test value is true but it is still not showing the desired result and showing the text of false value.

    Where I am going wrong?

  22. Ann Wan says:

    I am looking up a specific value from two tables I set up, one table has one rate, another has a different rate. I am using IF (VLOOKUP, true), (VLOOKUP2, true), false). How do I make the VLOOKUP2 value work? Thanks!

    • Hello!
      Please specify what you were trying to find, what formula you used and what problem or error occurred. Include an example of the source data and the result you want to get. It’ll help me understand the problem you faced better and help you.

      • Ann Wan says:

        Thank you Alexander!

        Here is the formula I am trying to make work:

        Rule: if Column J is in TableFX25 then Column I* (1-0.025), if column J is in TableFX35, ColumnI*(1-0.035) else Column I*(1-0.03).

        The fomula: N8*IF(J8 =(VLOOKUP(J8,'FX Calculation.xlsx'!TableFX25,1,FALSE)),(1-0.025),IF(J8=(VLOOKUP(J8,'FX Calculation.xlsx'!TableFX35,1,FALSE)), (1-0.035), (1-0.03)))

        Problem: Vlookup for TableFX35 isn't working - the value in column J isn't being recognized in TableFX35.

        Is it because the formula?

        Appreciate the help. And let me know should you need further clarification.

  23. Leonard says:

    Hi Alexander,

    I am looking for a formula that looks like a combination of Vlookup and IF function.
    following is my scenario:

    TAB 1 contains data of numbers negatives to positive, i want to reflect that data to TAB 2 with the condition , if value is less than zero like negative no. then return it as Zero "0" and if above zero then return as it is.

    Thank you so much,

    • Leonard says:

      I think i found it , please disregards my question above, thank you

      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%)

      This will do, any number multiply zero will equal to zero.

    • Hello!
      Please try the following formula:

      =IF(Sheet1!A1>0,Sheet1!A1,0)

      You can copy this formula down along the column.
      Hope this is what you need.

  24. Sada Hussain says:

    If i want to apply vlookup formula and want to make difference two reference column how can I did it.

  25. Ganesh Relekar says:

    Hi

    I have list of DMAs for which I need to vlookup states (col 2) but pick only which has max vehicle count in (col 3).
    what formula would you recommend?

    DMA State Vehicle Count
    ALBANY MA 2,272
    ALBANY NY 26,361
    ALBANY VT 830
    SANTA FE. AZ 1,033
    SANTA FE. CO 1,132
    SANTA FE. NM 26,917

Post a comment



Thank you for your comment! When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to your query. We cannot guarantee that we will answer every question, but we'll do our best :)