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

  1. I want to lookup and the "where to look up" is in a cell among other values. How should be the formula in this case?

    For example, I have my data with A,B,C,D,E, I want to know for those a specific information linked, but sometimes the cells could be filled like A ; B ; C or A ; B or A : E.

    The "where to look up value" is then not always alone in its cell like "A" to do the proper formula.
    Hope it is clear enough :)

  2. Hi!
    I have 6 columns.
    In first one - A i have a list of shop names, in the second one B I have code of selled products, and on the sixth column F i have quantity of this selled products:

    Table1:
    A B F
    Shop 1 Product 1 Quantity 1 (5)
    Shop 2 Product 1 Quantity 2 (4)
    Shop 3 Product 1 Quantity 3 (0)
    Shop 4 Product 1 Quantity 4 (3)

    In other table i need to extract from this first table for example The Quantity that Shop 3 is selled and to put it in exact cell.

    Table2:
    A B
    Shop 3
    Product 1 Quantity 3

    I tried with combination of VLOOKUP and IF, but withot the result i need...
    Please for your support!

    Thank you!!!

  3. I need to do vlookup only if date for same lookup value in one sheet is greater than date of that return value in lookup array in another sheet. what is formula for this please.

  4. hey magic excel guys,

    Scenario: part number "x" & "y" have different locations and each location has its own quantity of x & y. see below.

    Item No Location Qty
    x A 5
    B 4
    y A 3
    D 12

    I need to be able to lookup value x only in location A to get the quantity. (my excel sheet has 3804 rows of data similar to the above)

    Here's the formula i came up with:

    =IF(VLOOKUP(A1,table_array,col_index_num,false)=A, (A1,table_array,col_index_num,false), "Not available")

    In my mind, this makes sense, but excel thinks otherwise.

    Grateful for any help here.

    Cheers

  5. Hi, I am searching for a number in the other sheet and I want the answer in "Y" or "n". I will explain this in detail,
    For ex. if I enter anything in D4 and want to know whether that number is present in the other sheet or not. And I want that results in column V4 with the colour Green for "Y" and Red for "N".

    How can I make a formula for the above requirement as I have tried it using IF(IFERROR(VLOOKUP)) functions, but something is wrong.

      1. Actually still I did not got the proper solution, Currently I have been using the below formula for the above condition

        =+IF(D4="","",IFERROR(VLOOKUP(TEXT(D4,"0000000"),[LIST_aktuell_GesamtbestandCTeile_V1_AR2222.xlsx]Tabelle1!$B$4:$O$30000,2,FALSE),"N"))

        LIST_aktuelle_....is the sheet name.

        Can you correct if you have understood the problem.

        I have already done the conditional formatting for the Yes or No.

  6. Hi, can somebody help me please.. can someone give me a formula wherein.. "if I input any any TEXT on C1, "latest date will show in B1" and "number will show in A1". And a formula wherein "if I input TEXT in C5", it will also show the other values beside it in B and A column.

    Thank you so very much and God bless you.

    1. use this formula

      in B1
      =IF(ISTEXT(C1);"latest date";"")

      in A1
      =IF(ISTEXT(C1);"number";"")

  7. Hi, I would like a formula for the below criteria

    I have two sheets, the first fixed one contains the restaurant names with their fixed delivery charge in the column next to them, E.g Restaurant A (delivery charge 10), Restaurant B (delivery charge is 12), etc..

    The second sheet will contain the deliveries done for the month. So if the name of the restaurant matches its equivelant name in the first sheet, then the delivery charge is 10. If the second row also has restaurant A then charge is 10 again, if third row contains restaurant B, then delivery charge is 12.

    Thank you

  8. I want to have a function that will check the rows. Columns A to C can contain a value or word "Dummy". In Column D, I want to return the values that is not the word "dummy".

  9. Hi,

    I am trying to add a condition to the following VLOOKUP:
    =IF(A25="","",IF(A250,VLOOKUP(A25,'Product List'!$A$2:$H$73,8,FALSE)))

    The condition I would like to add, is if cell E25=Lemons then =IF(A25="","",IF(A250,VLOOKUP(A25,'Product List'!$A$2:$H$73,8,FALSE)))

    So that the =IF(A25="","",IF(A250,VLOOKUP(A25,'Product List'!$A$2:$H$73,8,FALSE))) only works if E25 has Lemons entered in the cell. Is this even possible?

    Thank you for your help. I have been trying to solve this all day (I am not an advanced Excel user). :(

    1. Hello!
      If I understand correctly, you can add another nested IF function.

      =IF(E25="Lemons", IF(A25="","",IF(A250,VLOOKUP(A25,'Product List'!$A$2:$H$73,8,FALSE))),"")

      I hope my advice will help you solve your task.

  10. I would need help on the following conditions please if possible.

    If Column A meet the criteria and Column B meet another criteria, return Column C.
    But if the Column C is blank, look for the next return value under Column C where Column A & B still both meets the criteria.

  11. Hi, I need exactly what describe in the scenario of "Vlookup with If statement: return True/False, Yes/No, etc." With only one twist.
    Imagine I have multiple rows of "Lemons" in column A with different values (10,6,etc) and I need the function to tell me true/false only if one of the "Lemon" rows hits the value of "0" value in column B ignoring the others (10,6,etc).
    Is that possible?

  12. =IF(AE3=$AQ$2,IF(AC3=$AR$2,IF(AI3=$AS$2,IF(AJ3=$AT$2,VLOOKUP(B3,B3:AM422,3,FALSE)))))
    I am trying to a command that says IF($AQ$2=N/A, false, otherwise give me the value).
    I was using this formula to pull data from a large data base using a drop down list. However, It is requiring me to choose all 4 criteria's, otherwise, it gives me the false result instead of giving me a number for one and false for the other.

    Let's say I have this headers for a drop down list: State City Tenancy (Elderly, Family, and Other) Construction Type (New, Rehab)

    Depending on the criteria's above, I commanded it to If(AE3-$AQ$2....VLOOKUP ((B3,B3:AM422,3,FALSE)) $AQ$2 is the state and such. AE3 is the criteria from the big data base.

    Depending on my criteria choice, i wanted it to give me the values for the different expenses i was looking for: Management fees, utilities, etc.

    The bottom line of my problem is that If I want to see all the properties in Indiana, with a tenancy type of Family and construction type of Rehab, and I don't care about which cites these projects are located so I select N/A, it gives me false for all criteria's instead of values for the criteria's I choose. How can I command it so that if I selected the stat, the tenancy Type, and the the construction type, it will give me the appropriate values. But since I don't want the city this time, I am trying to command it if City is "N/A), then give me false.

    1. Hello!
      If I understood your problem correctly, you want to define a N/A error in a cell. Try using the ISNA function for this.
      IF(ISNA($AQ$2), false, otherwise give me the value)
      Hope this is what you need.

  13. Hello - I am trying to verify if a delivery time was within the scheduled delivery window, can someone help with a formula?

    Arrival Time Departure Time Start Time End Time DELIVERED DURING CORRECT TIME SLOT
    8/1/2022 2:28:16 AM 8/1/2022 3:01:45 AM 0 300 TRUE

  14. Great article,

    I probably am missing something easy, but how can I use
    =IF('sales-breakdown-jul1-12-2022 '!A:A=A3,VLOOKUP("Liquor",'sales-breakdown-jul1-12-2022 '!$B:$D,2,FALSE))
    where A3 is a cell within a name bank. When I write the person's name in "John Doe" into the logical test, it works but I don't want to write a formula for each new person each time we run report.

  15. Hello Alex,

    Thank you for this great article. I only came to know of ISNA & IFNA after i read this. I have been struggling with this formula since morning as the formula works in some cells and shows N/A in some cells. I have a sheet that has to look up the country, if US, then check if it is a corporate or client , look up the pay range in the adjacent cell then give a value, If not US, there is no differentiation and picks the defined range for non-US. What bothers me is when i correct the formula by doing the single v-look up in the N/A cell, the value pops up, but when I drag the formula its a ruin.

    Could you guide me on what I should be doing.

    =IF(P11="United States of America",IF(AB11="Corporate",VLOOKUP(H11,'Main Pay Ranges'!Q11:R22,2,0),VLOOKUP(H11,'Main Pay Ranges'!E11:F22,2,0)),VLOOKUP(H11,'Main Pay Ranges'!E45:F57,2,0))

    1. Hi!
      I can't check the formula that contains unique references to your workbook worksheets, sorry.
      I think you need to use absolute references for search ranges: VLOOKUP(H11,’Main Pay Ranges’!$Q$11:$R$22,2,0)
      Maybe it will help.

  16. Hi
    in BS 2 value is 1 and i am trying to use below formula.

    =IF(BS2="1",(VLOOKUP(BG2,Sheet1!$F$6:$G$67,2,0)))

    but its still showing " False" in cell .

    Can you help me with this.

    Thanks you.

  17. I have created a dropdown menu y/n. If yes, then the formula should get a value in another sheet and if no, it should just be a 0.
    Yes/No - If yes, pick up value in cell x; if no = 0 - I have tried several ways. Someone that might have an idea?
    Thanks.

  18. Can you help with with what my formula format would be if I want to pull a cost from another tab if certain criteria matches in both tables?
    Ie. I have a master inventory list that has serial numbers costs, etc. When I enter the sale of that serial number on another tab I want it to auto populate the cost associated with that serial entered on the inventory tab. Is that possible?

    Thank you

  19. Hello, I am hoping you can steer me in the right direction. I have a spreadsheet for tracking monthly hours. It has a sheet for each month and a Summary sheet, which should show the sum of each person's hours for the year. There is a unique identifier (employee number) for each employee, so I set up a formula using SUM and VLOOKUP in the Summary sheet. I named a Range on each spreadsheet (jan, feb, mar, etc.), so it looked something like this (I am still experimenting, so the formula only goes to May):
    =SUM(VLOOKUP(A2,jan,3,FALSE)+VLOOKUP(A2,feb,3,FALSE)+VLOOKUP(A2,mar,3,FALSE)+VLOOKUP(A2,apr,3,FALSE)+VLOOKUP(A2,may,3,FALSE))

    The hitch: we start with a certain number of employees in the spring, and hire seasonal workers throughout the year. So the list of names in January might be 400, by April it might be 650, and by August 800. My formula works fine for the first 400 folks, but when I add more names to the spreadsheets for the following months (and extend the Ranges to include the new names), the Summary results come up as N/A for the new names. If I add an IFNA function, all new names after the first spreadsheet (jan) bring up "0" in the Summary instead of N/A:
    =SUM(IFNA(VLOOKUP(A2,jan,3,FALSE)+VLOOKUP(A2,feb,3,FALSE)+VLOOKUP(A2,mar,3,FALSE)+VLOOKUP(A2,apr,3,FALSE)+VLOOKUP(A2,may,3,FALSE),0))

    I am still very much an Excel novice, so any assistance would be very appreciated!

    1. Hello!
      I can't check the formula that contains unique references to your workbook worksheets. However, you are using the IFNA function incorrectly. Try to change the formula:

      =SUM(IFNA(VLOOKUP(A2,jan,3,FALSE),0)+IFNA(VLOOKUP(A2,feb,3,FALSE),0)+IFNA(VLOOKUP(A2,mar,3,FALSE),0)+IFNA(VLOOKUP(A2,apr,3,FALSE),0)+IFNA(VLOOKUP(A2,may,3,FALSE),0))

      Also, a pivot table is perfect for your tasks. Please have a look at this article: How to use pivot tables in Excel - tutorial for beginners.

      1. Thank you very much for your help - I am off to learn about Pivot Tables!

  20. Hi,

    I'm struggling to find the right formula to multiply units by rates.

    I have different materials and tasks with different units and rates are depend on quantities. Some of the units only have one rate with no conditions.

    I have a more than 2000 row spreadsheet and units also varies that means that the formula also need to find the unit on sheet 1. Rate criteria can also change on sheet 1.

    I'm looking for the price on Sheet2.

    I believe the below formula need to be combined with vlookup but I cannot get it to work

    Many thanks for your help!

    Niki

    Sheet 1
    Unit "Rate1(not exceeding)""Rate2(not exceeding)""Rate3(not exceeding)""Rate4(exceeding)"
    day
    h
    m (QB) 10 50 200 200
    m
    m2 (QB) 10 50 200 150
    m2

    Sheet 2
    Description Unit Quantity Rate 1 Rate 2 Rate 3 Rate 4 Price
    path m (QB) 10 11 13 13.5 14
    road m (QB) 51 5 10 15 20
    wall m2 (QB) 35 10 15 20 25
    wood m 20 11
    paint m2 150 12

  21. I have a dataset where a single person will have several records that look very similar except for the date column.

    So if BRAD has 8 apples as of Jan 1 (row 1) and 10 apples as of March 10 (row 2) and 4 apples as of April 4 (row 3), I would want to return a value of 4 because it is the most recent date.

    BRAD - APPLES - 1/1/2022 - 8
    BRAD - APPLES - 3/10/2022 - 10
    BRAD - APPLES - 4/4/2022 - 4

    Is there a way to accomplish this with VLOOKUP and IF functions?

  22. Im creating a gsheet for prorated leave balance, if employee reach 2 yrs in service she/he will have 14 leave credits, if reached 3, 4, or 5 yrs will have 15 leave credits.

    But if employee reached 1 year, it will be prorated. For example her one year fall on January 1 to 15, automatically she will have 12 leave credits but if her one year fall on January 16-feb 15, she will only have 11 credits, if one year fall on Feb 16-march15, she will have 10 credits and so on. How can I formulate that?

  23. I m working with two sheet in sheet 1 having primary column and in sheet 2 having primary column and sub column ..i compared two sheet and trying to get the match records . My requirement is need to get the subcolumn match name in sheet 1 note=Column names are string

    for ex -I tired this IF(Vlookup(Sheet1A2,sheet2A2:B210000,2,false)="Jan","yes","no")
    But not getting the correct result. Any solution?

  24. Hi!

    I'm wanting to first check a column to see if a person passes a level of cognitive ability before using VLookUp to determine their level of fit with the job. Specifically, if in column A it says "not fit" (meaning they didn't pass), I don't want to look at the rest of their scores (in another column) to determine their overall level of fit. How can I accomplish this? I feel like I need an If statement somewhere, but I don't know how to combine these together.

    1. Hello!
      Check the condition in cell A1 using the IF function:

      IF(A1="not fit","",VLOOKUP( ........))

      Hope this is what you need.

  25. Ive been following this thread with interest to find a solution to using vlookup based on a cell reference.

    Im trying to perform one of 3 vlookups within the same cell based on the value in a reference cell (ie: cell B3 contains either 1,2 or 3 which relates to data in a different sheet in the workbook) I then want to vlookup cell A3 in the corresponding sheet and return a value.

    My logic is =if B3=1 then vlookup A3 in sheet1 range A1:C100 and give me the value in column C else if B3=2 then lookup A3 in sheet2 same range and cell as before else if B3=3 then lookup A3 in sheet3 etc.

    Im struggling with correct format to perform the above so any tips are greatly appreciated.

  26. Hello Team :)
    I am trying to to use the IF function with a lookup table, using a value of 1 for males, 2 for females, to search and return the correct result depending on which gender applies.

    My current, formula is:

    =IF(C2=1,VLOOKUP(C2,B25:E30,2),VLOOKUP(C2,B25:E30,3))*1000

    The formulas for each age group are in two colums, column 3 for males, column 4 for females.
    I can't get the formula to choose the right column as designated by the 1 or 2 choice.

    I'm not sure if that is sufficient information.

    Additional information:

    Using Schofield's formula to calculate daily (basal) energy needs

    SCHOFIELD BMR EQUATION - MEGAJOULES

    AGE GROUP WEIGHT MEN WOMEN

    10 - 18 83 8.896 7.546
    19 - 30 83 8.125 7.182
    31 -60 83 7.637 6.360
    > 60 83 6.526 5.909

    A person over 60 weighing 83kg needs 6.526 mj (males) or 5.909 mJ (females).

    I just need to lookup to to choose according to the 1 or 2 designated - 1 will return 6.526, 2 will retun 5.909.

    Thanks!

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

      =IF(C2=1,VLOOKUP(C1,B25:E28,3,TRUE),VLOOKUP(C1,B25:E28,4,TRUE))

      C2 is 1 or 2
      C1 - this is the age in years.
      B25:B28 is 10, 19, 31, 60

      Read about VLOOKUP approximate match (TRUE) in this article.
      This should solve your task.

  27. can u help me with this scenario

    final numerical
    performance rating
    8.38 <-------------RATING BUT IT DISPLAY (VS)
    equivalent adjectival rating
    = (VS) <------ I WANT TO SHOW SOMETHING LIKETHIS
    THE DATA BELOW RANGE FROM
    9.5-10 = OUTSTANDING (O)
    7.51-9.49= VERY SATISFACTORY (VS)

    THANKS

  28. Hi,

    Have a peculiar query.

    The following table denotes achieve numbers in the unit tests.
    Name Unit 1 Unit 2
    A 100
    B 95 98
    C 85
    D 92 96
    E 65 85
    F 99
    G 85 92

    Whosoever have taken UNIT 2 test. That number will be considered, else UNIT 1 number.

    Pls help with formula

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

      =IF(C2="",B2,C2)

  29. I have the almost the same data with example number 1 but instead of it being zero (0), the column is blank (no data). How do I replace the (0) in the formula if the data in the column is empty?

    =IF(VLOOKUP(E1,$A$2:$B$10,2,FALSE)=0(what if the value of column is empty/no data?),"No","Yes")

    1. Hello!
      This formula works if there is a blank cell instead of 0. You don't need to change anything in the formula.

  30. Hi,

    I need help with a formula. I need to do a Vlookup or formula based on a location I type in the cell to reference that locations data. If I type a location in a cell (ex. Atlanta) I need to do a Vlookup based on location 1's (Atlanta's) data. If I enter location 2 (ex. Charlotte) in the cell I need to reference location 2's (Charlotte's) data.

    Thank you,

    1. Correct me if I'm wrong

      based on my understanding @hollie has 2 different set of data which are known as Atlanta and Charlotte as separate table and @hollie want to use those data set as vlookup point of refference for return value

      I'm still learning and advice my solution since I just get the undertanding of the formula 2 minutes ago

      Hollie need to combine Charlotte and Atlanta Table as one big table and ensure to use unique data as first column also using those unique value as data entry for serching return value

  31. I'm trying to do a lookup formula that if true will display "present" but if false, will look in another sheet to find a "present" mark. Is this possible?

    So if J Bloggs was absent on Monday's sheet returning "absent", but did attend on Tuesday's sheet, I want the tuesday's sheet mark to return so it is clear to see if anyone hasn't attended that week.

  32. Hi I am looking to subtract one lookup to the other.
    =(vlookup(T11,A7:H155,4,false))-vlookup(T11,M12:R80,5,false)

    But if the cell for the second lookup value is empty, i would like it to just display the first value. How to I go about doing that?

    Thank you!

  33. Hi! I am trying to write a formula that compares names across two different excel sheets. With that being said I wrote a formula that takes the value for a month (say August) and subtracts it from a prior month (July). The three values it can return are -1 , 0 , and 1. I only care when the resulting figure is a 1. Here is the formula I have so far:

    =IF('TB VI 2021.09.10'!E10-'TB VI 2021.07.31'!E10=1,1,IF('TB VI 2021.09.10'!E10-'TB VI 2021.07.31'!E10=0,"",IF('TB VI 2021.09.10'!E10-'TB VI 2021.07.31'!E10=-1,"",)))

    However, it is important that the formula pulls data from the correct names in August and in July. Sometimes, a name appears in a different cell spot across each month. I need to add something to this formula that takes the value from a specific name in one month (August) and finds that name in another month (July) and performs the subtraction (above formula)

    Let me know if this is feasible or I can elaborate any further

    1. Hello!
      Please check out this article to learn how to search multiple sheets using VLOOKUP.
      I hope my advice will help you solve your task. If something is still unclear, please feel free to ask.

      1. Help me to this problem

        1=8
        2=22
        3=40
        4=62
        5=86

        I WANT THE CODE TO DO THIS

        1.5=(value of 2 minus value of 1) "the answer is 14"
        14x.5 " decimal point of 1.5"
        Answer is 7
        7+value of 1
        answer is 7+8=15

  34. Hello I need help with a formula
    I need to extract details available in one column to a specific format.
    Attached please find the format, if anyone can help would be really helpful.

    Details I get in a column is:

    Roll Number:123456 House Number:789654 Last Name: rfhdfsdf Address: asdasfSFSFLKHKGKLHG

    I need a formula to extract it in below format to avoid manual task

    Roll Number
    House Number:
    Address:

    Why I am stuck is,

    1. the length of roll numbers and house numbers would change from a single digit to double or triple
    2. I do not want to extract last name as per the format, hence not able to use mid/left/right formula

    1. Hello!
      If I understood the problem correctly, you can extract the last name from the text using the formula:

      =MID(REPLACE(A1,1,SEARCH("Last Name:",A1)+11,""),1, SEARCH(" ",REPLACE(A1,1,SEARCH("Last Name:",A1)+11,""))-1)

      1. Thanks for your response Alex.

        No, the problem is I get below details in one cell "A2"

        Roll Number:123456 House Number:789654 Last Name: rfhdfsdf Address: asdasfSFSFLKHKGKLHG

        I have a different excel sheet, wherein I need to extract only Roll Number, House Number & Address from the cell manually, to different different columns of another excel sheet.

        Therefore need a formula.

        1. Hi!
          I wrote a formula to extract the last name. Replace "Last Name:" with "Address" in the formula and you will get the address.

          1. Great thanks Alex, it was resolved partly by getting the Roll Number and House number & Last name for me.
            But for House number Somehow not getting results for address. I tried changing the number from 11 to 7,8 - 13 and so on - considering the same logic by adding the number of character it has.

            Just help with this now and it will be resolved completely. Anyways a big thanks, it was such a great help!

            Sorry for the long chain of comments.

            1. Sorry read it as not getting results for Address
              Rest all sorted
              Sorry for Spamming

              1. I was making some idiotic mistake
                It has completed resolved
                Thanks for offering this platform sir
                And really sorry for the long chain. Actually I was really pissed off this manual task and was very overwhelmed when I got to learn about this platform in order to get some insights.

                Thank you so much!

  35. Hi Mr. Alexander, thank you so much because of you I learned a lot of things you also helped me before when I struggle searching for the right formula, I am new in Vlookup actually I started learning about it today, Im having trouble with this scenario, I wish to auto populate data in my table but there are so many criteria and range to consider I have column A for terms such as 12 mos,24 mos,36 mos each month have corresponding value for column B standard rate and column C advanced rate(I made it in dropdown list). I wish to autopopulate (column c row 6) the corresponding value when I input the term and whether I choose the standard rate or advance rate

    A - B - C
    mos - standard rate -advance rate
    12 - 1% - 4%
    24 - 2% - 5%
    36 - 3% -6%

    row 5 | mos - 24
    row 6| rate - standard rate (in dropdown form) |column C 2%

    I was able to do this but the problem is I can only choose to look for the data for standard rate I wish that even if I change from standard to advance it will automatically auto populate

    I hope you understand my concern Thank you

    1. Hello!
      Write down the formula to find advance rate versus standard rate in column C. Write down the formula to find standard rate versus advance rate in column B. If you write a value in column B or C, you will replace the formula with a value. But the formula in the adjacent column will find the value you want.
      I hope my advice will help you solve your task.

  36. Hi,

    I am trying to get the below formula to work. I don't know what I am getting wrong over here. Can someone please help with the same.

    =IFS(M3="",O3,O3="",R3),IFNA(VLOOKUP(M3,'Sponsor List Ref'!B2:C95,2,0),VLOOKUP(M3,'Sponsor List Ref'!$F$2:$G$6,2,0)))

    The formula works well with only IFNA part, but if i add the IFS before, it gives a #Value! error. Any input is appreciated.

    Thanks.

    1. Hi!
      The formula you wrote cannot be entered into a cell. Incorrect syntax. I don't know what you want to count. Formula =IFS(M3=””,O3,O3=””,R3) is complete. If you need to add the condition into it -

      =IFS(M3=””,O3,O3=””,R3, logical_test3, value_if_true3)

      Check out the syntax for the IFS function in this tutorial.

  37. I'm trying to create a template spreadsheet that always has VLOOKUP in Column C, going down 10,000 rows. This will check to see if the value in a specific cell in Column A is present in any of Column B, and returns "Yes" or "No" depending on the information. All good there.
    Sometimes I don't need all 10,000 rows, sometimes I do, but I don't want to change the template for this VLOOKUP. If I only have 50 rows with data in both Column A and Column B, I want Column C to be blank in every cell below C50 instead of returning a "no" result. This way when I filter Column C to only see the "no" cells it doesn't show all the results below C50 as well.
    For Visual:
    A B C
    1| Hello | Hello | Yes |
    2| Hi | Wave | No |
    3| 'blank'| Apple |'stays blank' |
    Currently I'm stuck on:
    =IF(ISNA(VLOOKUP(A1,$B$1:$B$10000,1,FALSE)),"No","Yes")

    1. Hello!
      Use the IF function to get blank if the cell in column A is blank.

      =IF(A1<>"",IF(ISNA(VLOOKUP(A1,$B$1:$B$10000,1,FALSE)),"No","Yes"),"")

      Hope this is what you need.

      1. Right on the money! Thank you for getting me out of that rabbit hole!

  38. Hi I want to do a vlookup of a cell that ends with N with multiple results
    Sample:
    Apple 13A
    Banana 76
    Mango 57S
    Apple 26N

    I want to vlookup apple with the result of 26N because it ends with "N". Please help me

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

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

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

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

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

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

        1. Hello!
          Use the IFERROR function.
          Instead

          J8 =(VLOOKUP(J8,’FX Calculation.xlsx’!TableFX25,1,FALSE))

          write down

          J8 =IFERROR(VLOOKUP(J8,’FX Calculation.xlsx’!TableFX25,1,FALSE),"")

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

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

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

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

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

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

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

    1. Compensation range is actually in B2:C5

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

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

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

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