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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  31. I want to compare two columns for comparing students if there is new admission or left school. I need the result in following month in a separate column. That column should contain three words: Enrolled in previous month(if found in both months), new addmission( if found unique), SLC/Struck Off(if not found at all in following month). I need to know the new entrants and leavers, so that I add and subtract these with opening strength(previous month) to get closing strength balance. Duplicate conditional highlights and leaves unqiue,but those who left the school will not be at all in the following month(second column). but it is of no use for me. If any vlookup with three condition is there, it will be fine.

  32. Hi. I have two spreadsheets with a list of more than 4k accounts each. I use vlookup with conditional formatting to determine if the same account number is on both sheets. But what I also need to do is, once it is established that the account number is on both sheets, update the balance on one sheet to the other on each account that is matched. Can you help me?

  33. Thanks
    good examples

  34. Hello!
    i am having problems in using following formula, condition is True we get result correct but condition is False we get error. kindly help, pl.

    IF(S2=VLOOKUP(S2,Reference!$Q$9:$R$28,1,0),VLOOKUP(S2,Reference!$Q$9:$R$28,2,0),N2)

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

  36. Hello,

    I have a question for you. Let's say I have the following content:

    Cell A1: "I love puppies"
    Cell A2: "I am a fan of dogs"
    Cell A3: "Cats are awesome"
    Cell A4: "Kittens are my favorite"

    I want a formula that will see if a cell contains the words "puppies" and "dogs" and return the result "Dog Fan"
    AND
    I want the formula to see if a cell contains the words "Cats" and "Kittens" and return the result "Cat Fan" - how do I do this?

  37. Hi i am trying to set up a vlookup conditional formula where if value is >=1 it should add 5 to the value and if it is 0 , then it should return 0 but i tried to set a =if(vlookup.....)formula and for 0 it is returning #N/A, Please help

  38. Hi

    How do i use the VLOOKUP and IF function to help me calculate my table whereby if a duplicate value is present then the function will help to average out these duplicate values based on the number of duplicate values there is e.g ( 3 & 3 = 3/2=1.5 , 3,3,3 = 3/3 = 1)

  39. sorry my question was like this

    I am looking for a value as below

    look up the cell value =3300.01,0,VLOOKUP(N74,'Pay As You Earn'!$A:$N,14))

    above formula is giving the correct value up to 12,785.00
    but amount beyond 12,785.00 that its giving value 0

    and

    2) =IF(VLOOKUP(N75,'Pay As You Earn'!$A:$N,14,0)<=3300.01,0,VLOOKUP(N75,'Pay As You Earn'!$A:$N,14))

    Where as the above formula is giving correct value beyond amount 12785.00
    but below amount 12785.00 its giving 0

    please see and inform the reason if you want i can share the excel workbook for finding exact cause.

    Thanks.

  40. I am looking for a value as below

    look up the cell value =3300,0,VLOOKUP(N74,'Pay As You Earn'!$A:$N,14))

    above formula is giving the correct value up to 12785.00
    but after that its giving value 0

    2) =IF(VLOOKUP(N75,'Pay As You Earn'!$A:$N,14,0)<=3300.01,0,VLOOKUP(N75,'Pay As You Earn'!$A:$N,14))

    Where as the above formula is giving value 0 up to 12785.00
    but after that its giving the correct value

    please see and inform the reason if you want i can share the excel workbook for finding exact cause.

    Thanks.

  41. I have a main dataset of about 500,000 line items. I want to lookup the items from 3 different datasets. Each of these datasets has got about 200,000 line items. I want to use the if and vlookup function to return a " Yes" or "No" answer.

  42. How can I use the if and vlookup formula using multiple data tables to return a "Yes" or "No" answer

  43. In Excel, I have 2 sheets. One is titled "Equipment" and the other is "Technicians". What I would like to do is, on the Technicians sheet, In cell F3, I would like it to look on sheet "Equipment" from the range of I2:I17 and look for Gene? If Gene is found, then display the data from the corresponding cell in range A2:A17. This should be displayed in cell F3 on Technicians sheet. Any help would be appreciated!

  44. Good afternoon,
    I have emailed support@ablebits.com with a file (AJB Test Data) that I need help with please. I currently have a long IF calculation running over many columns to obtain the correct data that I need, but there must be a shorter way of doing it using Vlookup or Index Match maybe? In the Data Tab I need to use columns A and B to look up the corresponding column and cell on the Data 2 tab, then place the answer in the Data Tab, column C against the corresponding row. In essence there are two cells to match up to locate the correct data. In my working copy there may be up to three cells to match! Help would be greatly appreciated. Thank you.

  45. Hello, I have problem to merge my data, can you help me, please? Can I get your email to send you my data? Thank you :)

  46. I'm not sure if this is possible, but figured I would ask. I have 1 column (354 rows) that include a unique query string in each and want to search that string for an account number (5-6 digits long). I have another column that lists all the possible account numbers to do a lookup against. Next to that column, I have a column with account names for each account number. I'm looking to see if it's possible in new column to return the account name associated with particular account number IF that account number is found in the query string. Let me know if I explained what I'm trying to do correctly and if this is possible without parsing out the account number from the long query string.

  47. I'll begin by saying I am relatively new to using Excel (~1 YR).

    I basically need to do the following:

    I have a list of about 10K different items that we sell that I use the data sort function to sort by A) total units sold, B) # of months the item sold in the past year, and C) total number of hits (sales order lines)

    Essentially the exact same items in three different lists (or one list and two tables) in different order.

    Basically I want to take the top 2000 items by units sold and use vlookup to find matches in the top 2000 items in the other two list sorts.

    I am thinking along the lines of a IF vlookup table1 AND vlookup table2 -- but I am not familiar enough to input the appropriate formula.

    Any advice would be appreciated.

    Thanks!

    ~Brian

  48. Finally got back round to looking at it.

    Thanks Doug I would of never thought of that formula at all !. I have done what you said and edited the +1 to +2 etc, and it continues down the list so thanks ever so much for the help.

    Daz

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

  50. There are great examples on here which I have used but struggling with something I am not sure is possible.
    I need a vlookup with an if that continues down the list.

    So if this was the data
    A
    1Tyre On site
    Tyre Warehouse
    Engine Germany

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