Comments on: VLOOKUP with IF statement in Excel

Whilst VLOOKUP and IF functions are useful on their own, together they deliver even more valuable experiences. Today, we will be looking at a few simple examples that will show you how to Vlookup with If condition in Excel. Continue reading →

Comments page 3. Total comments: 225

  1. 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.

  2. 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'

    1. Hello!
      You can learn more about fixing #N/A error in VLOOKUP formulas in Excel in this article on our blog.
      I hope I answered your question. If something is still unclear, please feel free to ask.

  3. 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

    1. 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. I want to have an if function which returns the value of the next cell if the value is zero

    1. Hi,
      If I understand your task correctly, the following formula should work for you:

      =IF(A2=0,INDIRECT(ADDRESS(ROW(A2)+1,COLUMN(A2))),A2)

  5. 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

    1. Hi,
      If I understand your task correctly, the following formula should work for you:

      IFERROR(IF(VLOOKUP(…) = "Y", "Yes", "No"),"Null")

      You can learn more about IFERROR with VLOOKUP in Excel in this article on our blog.

  6. I am trying to bring back column C based on the match in column A and the amount in column B. What would the formula look like?

    HS amount .375 = column c?

    Column A Column B Column C
    HS .420 Default
    HS .390 DSM
    HS .375 RSM
    AP .400 Default
    AP .350 DSM
    AP .300 EXEC

  7. Hello ,

    I need some support from you.

    For one excel document I need to separate numbers from text in two separates columns.

    In my case ,01234-23456-234556-ABCD and I want to separte only 01234-23456-234556 in one column and ABCD in other column and to keep this format.I tried to use the function .I tried to use the Excel function ''Text to Columns'' but it was not working fine for my case.

    Thank you.

    Best regards,

    Silviu

    1. Hello!
      The formula below will do the trick for you:

      =LEFT(A1,SEARCH("#",SUBSTITUTE(A1,"-","#", LEN(A1)-LEN(SUBSTITUTE(A1,"-",""))))-1)

      and

      =MID(A1,SEARCH("#",SUBSTITUTE(A1,"-","#", LEN(A1)-LEN(SUBSTITUTE(A1,"-",""))))+1,50)

      I hope my advice will help you solve your task.

  8. Hello,

    I have a questions, below is the scenario in excel. I keep getting inconsistencies in my formulas

    Cell A14=John, B14= Smith, C14=123-456-7890, D14=jsmith@gmail.com, E14=SE, F14=Appointment or Pending

    If cell F14=Appointment then cell G14= the name in cell A14. But if cell F14=Pending then cell G14= blank or false.

    How do I write this in excel? Can you please help?

    1. Hello!
      If I understand your task correctly, the following formula should work for you:

      =IF(F14="Appointment",A14,IF(F14="Pending","","No"))

      Hope this is what you need.

  9. Hi, how can I combine vlookup, if, isblank, and ifna?

    I have if the result is found in vlookup then it should be “OK”, but if it is N/A it should be “ADD TO LIST”. I also want to include a formula wherein if the cell is blank it will have “ENTER DATA”. These all can be in one formula? Thanks!

    1. Hello!
      If I got you right, the formula below will help you with your task:

      =IF(ISBLANK(E1),"ENTER DATA",IFERROR(IF(VLOOKUP(E1,$A$2:$B$10,2,FALSE)"","Yes"),"ADD TO LIST"))

      I hope it’ll be helpful.

  10. Hello,
    how I can use this formula when I want leave the current value
    if the cell is empty so it will write a value
    but if it's not empty ignore it

    1. 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.

      1. I have 2 sheets
        1st sheet include some columns (A,B ,and C), one of the column (C) I'm using a vlookup to retrieve values from sheet #2
        daily I update the sheet #2 and it should to update also the column (C) in sheet #1
        ie. rows # 2,4 have a values - I don't want to lose these values - but rows # 3,5 is empty
        I need to use if statement with vlookup to update row # 3,5 and any other empty value in C column and ignore the other rows 2,4 because it has a values already

  11. Hi,

    Really impressed. I had one question.
    What should be the formula if in given example, like I had one seller i.e. Olivia with more than one products in sales and I want to pick one specific product's figure.
    Looking for a prompt response.

    1. Hello!
      I think the "How to VLOOKUP multiple values in Excel with one or more criteria" guide will answer your question. Read here.

  12. Hello!,
    I have an excel file, where on one sheet there are some employee names and data related to whether they are in the office or not. on the other sheet I have employee names only. Names on both the sheets are same. I just want to have a field on second sheet, where i can have same information related to whether the employee is in the office or not?
    what function should i use?
    Thanks in advance!!

  13. Hi there,
    I have one file wherein Column A and B are filed with some data. Now I have 2nd file wherein there are two cells with data as per file One.

    Now I want a formula to check and give result as Yes or No if the data in both the cells in 2nd file are filled as per data from File one.

    TIA

    1. Hello!
      I think the article on searching VLOOKUP for multiple criteria will help you. Read here.
      I hope this will help, otherwise please do not hesitate to contact me anytime.

  14. Hi,

    I Need to check 2 texts in 2 different columns, if both are in same row, then i want the row number. What would be the formula for that case?

    1. Hello Giridhar!
      If I understand your task correctly, please try the following formula:

      =IF(A1=B1,ROW(),"")

      I hope it’ll be helpful.

  15. Hi Svetlana.. Thanks for this informative article as always.. I wanted to use dates as a criteria in vlookup.. for example I have some bills data and I want to lookup ex party sales value in suppose December to March period..How can we do that...

    Thanks.

    1. Hello Amit!
      The VLOOKUP function can pull just one value from your table. If you need to count sales for a certain period of time, you have to sum a great number of bills to get the result. It means that VLOOKUP doesn’t suit for this task.
      I recommend to use the SUMIFS function to get the sales result for several months.
      Please see the detailed instructions on how to work with this function here: https://www.ablebits.com/office-addins-blog/excel-sumifs-multiple-criteria/
      Hope this information will be helpful for you.

  16. 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

    1. 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

  17. 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

    1. 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.

      1. 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

        1. 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! :)

          1. Thank you again, Natalia.

            Happy New Year!

            Best,
            Magnus

  18. Hi,

    Could we do 'IF combined VLOOKUP' search a lot of data where located from more than one tabs? if possible how do I do this?

    thanks in advance.

  19. Sorry pressed enter by mistake.In simple terms I need a formula that looks up an entry and if there is also specific text in the same row returns a YES.

    It only seems to work on the first hit in the list.

    I have this formula in

    F2

    =IF(VLOOKUP(E1,$A$2:$B$9,2,FALSE)="Warehouse","YES","NO")

    and this in f3

    =IF(VLOOKUP(E1,$A$2:$B$9,2,FALSE)="onsite","YES","NO")

    The word tyre in e1 !

    But issue

    M y first line has A2 B2 has
    Tyre Warehouse the formula returns YES Which is correct

    If in my second A3 B3 it has

    Tyre onsite

    This returns a NO which is incorrect.

    If I go to the cell above A2 where it says "Tyre" and BT is "warehouse" and delete TYRE it then returns YES against "onsite" formula .

    It like it stops searching after the first hit

    on tyre warehouse

    In reality both those formulas should present a YES

    I basically want to look up a value in a table and if a entry on the same row is a specific piece of text give me a yes.

    The value may be repeated several times the text will be one of a few things.

    I appreciate I will need a formula per entry "warehouse", "onsite .

    Any help would be appreciated.

    1. Darren:
      I think you'll be better off using INDEX.
      I built a little sample sheet using your data and I had some fun with it.
      So, where the location data is in D22:D33, the items are in C22:C33 and the item you're looking for is in H22, the formula looks like this:
      =INDEX($D$22:$D$33,SMALL(IF($H22=$C$22:$C$33,ROW($C$22:$C$33)-ROW($C$22)+1),1))
      There are a few things to note about this. First off it is an array formula so when you enter it into the formula bar put the cursor in the formula somewhere and click CTRL+Shift+Enter at the same time. When you do you'll see the curly brackets around the entire formula.
      Second, the last "1" in the formula is telling Excel which instance of the item in the list you want to return. If you change the "1" to "2" the items second instance in the list will be displayed. Change the "2" to "3" and the items third instance will be displayed.
      This will be useful if you enter "First Location" in cell I22 and "Second Location" in I23 and so on. Then enter the formula in H22 with the "1" and enter the formula with a "2" in H23 and so on. You're returning the location of each of the items. This way each of the items location can be displayed by selecting the items instance in the list. You can show where the Tyre is in every location. 1 might be warehouse, 2 might be Germany, 3 might be On-site, etc.
      Any questions, please ask.

  20. Hi,

    Suppose if the value of column A = " exact as X"and value of column B="exact as Y", formula should result the value of Column C.

    1. Deepak:
      Where the sum of column A is in A10 and the sum of column B is in B10 and the value you want to display is in C10 the formula in an empty cell is:
      =IF(AND(A10="exact as X",B10="exact as Y")C10,"No Value"))

  21. Hello,

    I've created a spreadsheet across 3 tabs which shows performance data from a team of almost 40 people.

    What I have been asked to do is create a separate tab whereby if we enter a name within a certain cell, all of their individual data appears from the various tabs. Can someone point me in the right direction to making this a reality? I'm a novice when it comes to Excel but can pick things up quickly enough if given a sense of direction...

    Thanks in advance!

    1. Chris:
      I would try the VLOOKUP and see how that works. You can use the INDEX MATCH option as well.
      The article, "Excel VLOOKUP tutorial for beginners with formula examples" here on AbleBits would be a good place for you to start. In your case the VLOOKUP from another worksheet might be the way to go.

  22. 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.??

    1. Try this expression:

      =IF(Value>=85,"500",IF(Value>=90,"800",IF(Value<850,"0")))

      1. =IF(C4>=90,"800",IF(C4>=85,"500",IF(C4<85,"0")))

  23. 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

    1. 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?

  24. 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

    1. 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.

      1. HI SVETLANA,
        i have a problem during applying of v lookup i have two sheets which are the first sheet only material description and second one have material code and description i want to apply material code in first sheet but during this only applied single description while came duplicate description the formula shown #NA.please suggest.
        Image:
        =VLOOKUP(G3,Sheet1!D1:E965,2,FALSE)
        HIGHLIGHTER 6000000026
        HIGHLIGHTER #N/A

      2. 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

Post a comment



Thanks for your comment! Please note that all comments are pre-moderated, and off-topic ones may be deleted.
For faster help, please keep your question clear and concise. While we can't guarantee a reply to every question, we'll do our best to respond :)