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 6. Total comments: 375

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

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

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

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

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

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

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

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

  10. IF A2 IS A BLANK CELL AND I WANT TO VLOOKUP IT UP AND BRING A VALUE IN THAT CELL, WHATS THE FORMULA?

  11. I have three columns for Aluminum pieces.

    Column A is the shape: Square, angle, circle, etc.

    Column B is the size of the shape: 2"x2", 3" x3", 2"x3", etc. Column B is a drop down with Data Validation that only pulls sizes based on the shape in Column A.

    Column C is the weight of the shape per foot. I currently have a V-Lookup for this, but it is pulling based on the size of the shape. My problem is, I need it to pull based on the shape, and the size, not just the size. This is because you might have a 3" x 3" angle, or a 3" x 3" square, and the weights are vastly different.

    I'm thinking I need some sort of IF and Vlookup, but I can't quite figure it out. Any help from y'all experts would be much appreciated.

  12. how to use if and vlookup formula for getting number- if negative then zero and if positive then same number?

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

  14. Dear Svetlana,

    if I have a entry, with start date of 01.01.2018 and the age range b/w 0-60 with elapsed year as 2 then I want the result to be S1 and similarly if I have the start date of 01.01.2019 with age range of 0-70 and elapsed year as 2 I want the result as S3, below is the problem table and the desired result I want request you to suggest me a formulae that can help me get the desired result.

    Age elapsed years start date desired result
    0-60 2 01.01.2018 S1
    0-70 1 01.01.2018 S2
    0-70 2 01.01.2019 S3

  15. Hello ,

    Why when I used the function IF with VLOOKUP ,some returns are correct some are not correct.The return in my case is ''TRUE ''even the the item is not present in the second sheet?I dont understand where is the problem?What is wrong?

    =IF(ISNA(VLOOKUP(C6;Sheet4!$C:$C;FALSE));"FALSE";"TRUE")

    Also the combination from below has the same issue ,is working but not 100% accurate

    =ISNUMBER(MATCH(C6;Sheet3!$D$3:$I$12;0))

    In my opinion should be very easy to be used, both are not working as expected.

    Is there any issue related to the cell formating?

    Please help with this issue.

    Thank you in advance.

    Silviu

    1. Hello!
      Unfortunately, without seeing your data it is difficult to give you any advice.
      VLOOKUP does not find the data it needs. There may be extra spaces or non-printable characters in your text. This often happens when importing data from another program. The numbers can differ by some sign after the decimal point, which is not visible on the screen. This often happens when numbers are calculated with a formula and are not entered by hand.

  16. I have two documents. One document contains errors and errorcodes. If the errorcode = N43 then I want the errordate, case number, and error description to populate in my other document. However, I only get #N/A in my formula results.

    Please help

    1. Hello!
      I’m sorry but your task is not entirely clear to me. For me to be able to help you better, please describe your task in more detail. Please specify what you were trying to find, what formula you used and what problem or error occurred. Give an example of the source data and the expected result.
      It’ll help me understand it better and find a solution for you.

      1. The project is for cases that get sent to a client. When there is an error for the case loading into the client's system we get an error (errorcode = N43). I need to track the date that each case errored. The error date, error code, case number, and several other columns of data is in one sheet. I am trying to create a 'front page' that will only pull the case, error date, and error code when the errorcode = N43. I tried this formula: =IF('[06 2020 ANG Medicare Rejects.xlsx]Combined'!W2="N43",'[06 2020 ANG Medicare Rejects.xlsx]Combined'!C2, " ") but it does not have the vlookup.
        This is an IF/VLOOKUP formula that I have tried but only get #N/A as a result. =IF(VLOOKUP("N43",'[06 2020 ANG Medicare Rejects.xlsx]Combined'!$A$1:$W$65536,3,FALSE),'[06 2020 ANG Medicare Rejects.xlsx]Combined'!$E$2,0)

        1. Hello!
          It is very difficult to understand a formula that contains unique references to your workbook worksheets. For the same reason, I cannot check her work.
          Write down the formula as it is, without links to other files. Give an example of the source data. Write what result you want to get from this data.
          Please describe your problem in more detail. It’ll help me understand it better and find a solution for you.

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

        1. Hello!
          Unfortunately, without seeing your data it hard to give you advice.
          A formula can only change the value of the cell in which it is located. If a value is written in a cell, then you can change it either manually or with a VBA macro.
          Read how to VLOOKUP across multiple sheets in Excel here.

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

  19. You saved my day thank you especially replacing NA part.

  20. Hello,
    I have two Sheets. I want to match three columns from sheet1 to sheet2 and when it finds/ not finds all values then it will return yes/no. how to do it?

  21. Hi, I'm trying to utilize a vlookup first, and if the value is not in the list I want to apply an IF statement. I'm trying with the below formula but it's returning #Value!. Any ideas? Here's my Vlook formula which works =VLOOKUP(A11,'For Reference'!A:B,2,0) and here's my IF statement which works =IF(C11="F4","Critical",IF(C11="F3","Significant",IF(C11="F2","Important"))) but I can't seem to merge the two. I've tried several variations of =(VLOOKUP(B11,'For Reference'!A:B,2,(IF(C11="F4","Critical",IF(C11="F3","Significant",IF(C11="F2","Important")*0)*0))))
    Any help is greatly appreciated!!

  22. Dear all help me to out this problem.
    I have one sheet where 2 collumn A & B.
    Under Collumn Name(A) 4 data like A1=Name, A2=Ram,A3=Ram and A4=Ram
    Under Collumn Salary (B) 4 Data Like B1=Salary,B2=(Blank),B3=(Blank),B4=5000
    I want formula which give me Data of B4=5000
    Formula Start Sarch the data from top and when get Blank then scanpe and give rusult after blank like B4 Value 5000.
    alway scape blank and give return data after Blank

  23. I'm here just to say thank you, it really helped me.

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

  25. Hi,
    Have a nice day.I have a problem in excel.I made a automated worksheet monthly, there i apply some of formula.I sumif my unique product and i needed here remarks but daily worksheet contains multiple times blank cell and sometimes remarks like as compensation.In this situation how i can use if and v-lookup combined formula to get that remarks.

    1. Hello!
      I’m sorry but your task is not entirely clear to me.
      Please describe your problem in more detail. How exactly are your comments recorded? how is the text in the cells? Where are these cells located? Where are the empty cells? Include an example of the source data and the result you want to get. It’ll help me understand your request better and find a solution for you. Thank you.

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

  27. i have required formula for below example
    Month item code Required remark from formula
    jan 1 exsting
    jan 2 exsting
    jan 3 exsting
    jan 4 exsting
    jan 5 exsting
    jan 6 exsting
    Feb 7 New for Feb
    Feb 8 New for Feb
    Feb 9 New for Feb
    Feb 1 exsting
    Feb 2 exsting
    Feb 10 New for Feb
    Feb 4 exsting

  28. Hi,
    Meanwhile I have also tried the below formula, and an Alert popped up reading "You've entered too few arguments for this function."
    =IFERROR(IF(OR($I$16="Grimaldi Lines"),VLOOKUP(C20,Mapping_Product,8,FALSE),IF(OR($I$16="Normal Shipment"),VLOOKUP(C20,Mapping_Product,7,FALSE)," ")))
    Appreciate your kind help.

  29. Dear Svetlana,
    I am currently using the below formula that works perfectly well. However, I need to eliminate #N/A in my empty cells.
    =IF(OR($I$16="Grimaldi Lines"),VLOOKUP(C20,Mapping_Product,8,FALSE),IF(OR($I$16="Normal Shipment"),VLOOKUP(C20,Mapping_Product,7,FALSE)," "))
    Whilst thanking you for anticipated kind and prompt attention, I look forward to hear from you.

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

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

  32. Hi, Having an issue with Vlookup returning an incorrect value
    formula: =IF(VLOOKUP(A2,Sheet2!A:C,1,FALSE)=A2,Sheet2!B2,FALSE)
    All the data is incorrect from the point of Jill in first sheet. It returned the figure associated with Pete (2nd sheet).
    first sheet with results (formula is in local column)
    global local first name
    1 23 joe
    3 45 jim
    5 15 jack
    7 300 jill
    9 23 joanne
    11 90 Joan
    13 12 John
    15 38 Jackie
    17 75 Jorge
    Sheet 2 that the data is being pulled from.
    global local first name
    1 23 joe
    3 45 jim
    5 15 jack
    6 300 pete
    7 23 jill
    9 90 joanne
    11 12 Joan
    13 38 John
    15 75 Jackie
    17 83 Jorge

    Can you help me fix this?

  33. Hello,
    Can anyone please help me with a search sheet formula that I have been trying to work on but haven't been successful?
    I have a sheet that looks up from a big data of vehicle parts.
    the criteria of my search is vehicle model, model year, part component and whether its genuine or oem or aftermarket. What I am looking for is the price which I have on a column.

  34. Hi,

    I need If and Vlookup logic together, but i should not get #NA error.

    =IF(VLOOKUP(C5,$A:$B,2,0)=$O$2,"AB","PR")

    In this case if the value in cell C5 is not in the range it's throwing #NA error. how to get it without that error.

  35. =IF(VLOOKUP($E$10,$E$10:$E$12,1,0)="AC",IF(AND(F10>=0,F10$L$10,F10=0,F10$L$15,F10<$L$16),(F10-L15)*3+$M$15,0)))))))

  36. Hi,
    I have a H2 which has 10 in
    In cell J2 i have 0 in
    J2 Cell can change (sometimes it will be 0 sometimes it might be 15)
    In cell k2 i need to calculate if j2 = 0 then leave blank however if j2 = more than 0 return whats in h2?
    Can somebody help me please!

  37. I need to use a formula to look up a value in a column (we'll call this #1), based on the value in another column (#2), and then finally return the value in the column to the left of #1 based on whether it not it matches a certain word. Would vlookup or index match work better?

  38. =IF('20182019EmpSW'!$A$2:$A$122246="2019Ogos",VLOOKUP($A$5:$A$9000,'20182019EmpSW'!$B$2:$AA$122246,17,FALSE),VLOOKUP($A$5:$A$9000,'20182019EmpSW'!$B$2:$AA$122246,18,FALSE))

  39. I'm trying to add an IF VLOOKUP with multiple search parameters - the idea I had was the following:

    =IFERROR(VLOOKUP([@[LOCATION_NAME]],'SP Locations'!A:B,2,0)=No,VLOOKUP([@[LOCATION_NAME]],'SP Locations'!A:E,5,0)),
    IFERROR(VLOOKUP([@[LOCATION_NAME]],'SP Locations'!A:B,2,0)=Yes,VLOOKUP([@[LOCATION_NAME]]&[@DEPTID],'SP Locations'!C:E,3,0))

    I was wondering if it was at all possible to string the two IF searches together?

  40. I need to return a text based on the result found in the VLOOKUP function on the other tab.
    =IF(A2="","-",VLOOKUP(A2,'Current RP - RQTY (IMS)'!A$1:F$5000,5,FALSE))
    If the following are found this is what I need returned to the cell... Can anyone help me out of the ditch on this one?
    1 = RawMaterial
    2 = Formula
    3 = Container
    4 = Substrate
    Thank you in advance for any help you may have!!!

  41. How to add vlookup along with below formula
    =IFERROR(IF(AND(N5>$AN$2,N5<$AO$2),N5,"-"),0)

  42. Can you tell me what is wrong with my formula? If my 'if statement' is true, I get the appropriate response of 62494, however, if my 'if statement' is false, my vlookup is giving me a #ref! error? UGH... helps if I include the function formula... sorry :) It's below:
    =IF('Travel Expense Voucher'!$F$5=2,62494,VLOOKUP('Travel Expense Voucher'!M15,'Tcodes and Ecodes'!C11:D12,'Tcodes and Ecodes'!D11:D12,FALSE))

  43. Can you tell me what is wrong with my formula? If my 'if statement' is true, I get the appropriate response of 62494, however, if my 'if statement' is false, my vlookup is giving me a #ref! error?

  44. Sir/mam i need your help. I have a google sheet with of option chain data of multiple stocks. For each stock there are multiple strike prices. I need to get specific strike's premium when i put stock name in a cell. I tried to use IF and VLOOKUP together i got the results but the problem is,there are 70 stocks so i have to write the formula for each stocks that made it very very lenthy and time consuming. Please guide me. Thank you...

  45. can this combination work for date formulas? i would like to create multiple formulas to find a list or sequence of dates to match any one person's payday. so that we dont have to rely on making a mistake on a paper calendar. so if someone gets paid weekly, bi weekly, semi monthly, on first of the month, or even on the 2nd, 3rd or 4th wednesday of the month.

  46. I have three receipt date against one material code say X and in another excel file against material X i want to pick up latest date how can i do this
    e.g.
    material code - receipt date
    X - 3-Sept-2019
    X - 14-Aug-2019
    X - 14-oct-2019

    Now i want to pick up latest date i.e. 14-oct-2019 against material X

    which formula to use

    Please guide

    Data is huge and in above case, i just gave you one sample.

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

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

  49. Use simple conditional formatting a simple condition make false disappear make condition if there is false then text color is white.

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

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