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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  15. =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)))))))

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  31. I am trying to make this comparison check work in Excel 2016
    =IFNA(INDEX('Redundancy Details'!D6:D7, MATCH('Redundancy Details'!K6:K7, 'Changing Roles'!E2:E3, 0)), "Not found")

    I search across several tabs and I compare D6 with K6 and if they are the same then I want to show the value of D6 however I receive the #VALUE! error indicating that a value used in the formula is of a wrong data type, I tried to change values from text to numbers but have the same result any suggestions?

  32. A B C E F G
    a MDF 15 10 a MDF 15
    b MDF 10 5 b MDF9

    kindly who used vlookup if Clom A and Clom E consider match and B and F apply vlookup

  33. Can somebody help me to find a formula for the items ("A,B,C") of column a .e.g,
    if the same items in column a have dates in column b for all the respective cells, should give result complete otherwise incomplete.
    Column (a) Column (b) Result Column (c)
    A 12-Apr Completed
    A 13-Apr Completed
    B 13-Apr Incompleted
    B Incompleted
    C Incompleted
    C Incompleted
    C Incompleted
    C Incompleted

  34. I have 2 IF formulas that I need to perform on 1 cell to determine the outcome, how do I combine them into one so if the first one applies then do this but if it doesn't then do the second? These are the 2 formulas that work independently

    (IF(VLOOKUP(B2,'AT Import'!C:H,6,false)="NP",(VLOOKUP(('Client allocation'!B2,'AT Import'!C:D,2,FALSE)-365),(VLOOKUP('Client allocation'!B2,'AT Import'!C:D,2,FALSE)))

    IF(VLOOKUP(B2,'AT Import'!C:I,7,FALSE)="ok",(VLOOKUP('Client allocation'!B2,'AT Import'!C:D,2,FALSE)+365),(VLOOKUP('Client allocation'!B2,'AT Import'!C:D,2,FALSE)))

    It's driving me nuts thanks!

  35. Hello Team,
    I have attendance data of 4 years. i want to see the summary of one person, so i am looking any formula which can take the desire value from that sheet.

  36. I want an if statment Vlookup that instead of replacing the N/A it simply wouldn't change it at all.

    For example I have pending, declined, approved.

    I want a Vlookup to change from Pending to Approved or Declined but sometimes we update the rest on the second day. So I would want which wasn't found to be as it is and the Vlookup doesn't change it. Is that possible?

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

  38. i want use VLOOKUP between tow dates like i have January month data but from there need to pick only 15 January to 25 January data as per dates...

  39. 1000 KA 3% =30 (MAXIMUM 25 YA MINIMUM 3%)

  40. Hi
    I have two sheets one is for staff another for day request
    In the first sheet for "staff" column for 'Leave Type'such as sick leave or vacation leave, and another 'start date'the date of starting of the leave
    In the second sheet, there is a cell "Date of presenting the leave"
    How I can in the cell of Date of presenting the leave, write formula to calculate the following ;
    if the type of the leave is vacation leave the date will be before 3 days from the date of this leave which existing in column of 'start date'for this leave
    and if the type of the leave is Sick leave the date will be after 3 days from the date of this leave which existing in column of 'start date'for this leave

  41. looking to create a spreadsheet with some complexity with user input and data supplied.
    Willing to pay for its creation, but need to discuss with someone for details? what is the best forum to find someone?

  42. How to combine this 2 formula?
    Need help.
    =IF(LEFT(E2,1)="B","YES",IF(LEFT(E2,1)="W","YES","NO"))
    and
    =IF(L2="B999","Y",IF(L2="W999","Y",IF(L2="DB10","Y",IF(L2="RC10","Y",IF(L2="TTST","Y",IF(L2="UTST","Y",IF(L2="USTS","Y","N")))))))

  43. Hi All, Please kindly help me check if the below formula using if and vlookup is wrong?
    =IF(VLOOKUP(A5,A2:A4,1,FALSE)=0,"No","Yes")
    I got the outcome of the vlookup value as yes which is correct but for value that were not found, I`m getting as #N/A instead of the suppose return value of "No"

  44. Hi I need a how to applyy comdition& formulass for one equation /one bracaket)

  45. I have origin as mumbai and destination as a ahmedabad and
    Origin Dst Destination Base Fuel Distance
    Ahmedabad BTH Bangalore 78.11 1518

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

  47. Hi...
    I have list of employees details with unique employee id in one sheet and in another sheet i have only employee id. If i type Resigned against one worker in sheet 1 means that workers code should be highlighted in sheets 2..pls suggest any formulas

  48. I have a list of sales with sold date for different months.
    I need a formula to pull sales by month.
    any ideas?

  49. Hi,
    I have query regarding find/search and vlookup.
    I want find specific text from cell (string) and retrieve data from vlookup table by using this specific text.
    e.g. “This is a boy” or “This is a girl” is in one cell.(there will more than 100 sentences) In Vlookup table Boy = Male and girl = Female.
    I want display Male or Female in another cell of same sheet depends on cell string contains boy or girl.
    Regards,
    Pradeep

  50. thanks rajesh peshiya for your interest in my problem solution,below is format of my data and requirement.
    SID-Sept SID-Oct (comparison)"

    10910 10910 Existing Student
    9116 9116 Existing Student
    11229 11229 Existing Student
    11769 11769 Existing Student
    11066 12346 New Addmission
    6386 10967 New Addmission
    11424 5662 New Addmission
    11770 New Addmission
    9988 New Addmission
    Stuck Off
    Stuck Off
    Stuck Off

    Common means old student
    unirque in Sept means new addmission
    Not found in Oct means Struck Off

    My requirement Summary
    Opening Strenght(1-10-18) 7
    Add New Addmission 5
    Less S/Off -3
    Closing Strength(30-10-18) 9

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