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

  1. I just have to say that this is a great summary of all the simpler ways to optimise VLOOKUP with IF.

    Simple, to the point, and very very helpful and useful.

    Thanks for writing this.

  2. =IFERROR(IF(VLOOKUP(B35,$S$3:$AB$209,10,FALSE)=K35,"",=VLOOKUP(B35,$S$3:$AB$209,10),"")

    Im in Row 35 because this was an example of Vlookup return false. I want the code to if false insert the value from the 10th column of the vlookup section

    (I am using two sections of data to compare dates, if the date has updated it will return false which I currently have marked "check" and then I have to manually go back and check what the date was updated to in the database. The new date is already listed in the 2nd section of data but I want it to automatically populate the cell with the date instead of searching manually.)

    The current formula doesnt work but I was hoping it would show what I am looking for.

    1. I can't understand your formula and check it as I don't have your data. I can't guess what result you wanted to get.

      1. Got it figured out

        =IFERROR(IF(VLOOKUP(B35,$S$3:$AB$209,10,FALSE)=K35,"",VLOOKUP(B35,$S$3:$AB$209,10,False)),"")

        For anyone needing a code like this

        If Error Blank
        If true Blank
        If false display new value.

      2. If error, leave it blank. If true, leave it blank. If false show new value.

      3. I want a return false to display the value which is listed in the 10th row of the searched section

  3. I don’t know if this is quite what I’m needing (probably far more advanced for me). I have 2 inventory sheets, Sheet1 with running totals of inventory incl column with prices. Sheet 2 is inventory taken by various sub trades. I’m trying to have sheet 2 calculate the individual costs per item taken on a specific entry. For simple example:
    Sheet1 Totals Sheet 2
    Item # Quantity Cost Item Quantity Job# Cost Date
    1234 10 1.25. 2345 2 111 3.00. 7/18
    2345 10 1.50. 1234 3. 121 3.75. 7/18
    3456 10 1.75

    Maybe ill answer my own thought, but I need Sheet 2 to input column D with IF A2 range is (Sheet1A2:A3)*(sheet1C2:C3) multiplied whatever the quantity is in Sheet 2 B2.

    Is this even possible? I have about 150 items and would hate to have to put criteria for each individual item.

  4. I am trying to pull data based on multiple criteria. I have tier levels numbered 1,2,3, etc. Each tier level has amount in another column. Each customer has between 1 & 7 tiers. I need to lookup which amount makes up that tier level for each customer. (ie; customer #1 is currently at tier 2, what is the amount associated with tier 2 for that customer #).

    Example:
    Customer # Tier Level Amount
    1 1 $0
    2 $1,000
    3 $2,000

    Thanks!! :)

      1. Thanks! That is close, but I need to reference a different cell for the value and they are on multiple sheets as well. I am not able to paste a screenshot so hopefully you can decipher this. ;)

        Sheet 1 - Column A has the customer number (each customer number has 1 row), Column I is where I need to add the formula to find the Lower Tier Amount for that customer.

        A H I
        Cust Lower Tier Rank Lower Tier Min
        61523 - ??? (needs to return 0)

        Sheet 2 - Column A has the customer number (each customer number has 1-7 rows of tier levels), Column I is the Lower Tier Amount, Column Q shows the current lower tier number (ie, tier 1, tier 2).

        A I J P Q R
        Cust Volume From Volume To Current Tier Lower Tier Higher Tier
        61523 0 700,000 1 - 2
        61523 700,000 1,000,000 0 (1) 1
        61523 1,000,000 999,999,999,999,999 0 (1) 1

        Thanks!!! :)

        2. Sorry, I knew it would mess up that formatting. A H I & A I J P Q R are the columns and the data for each column is listed below it (but bunched together).
          Sheet 1 - Column A - Cust, (61523) Column H - Lower Tier Rank (-), Column I - Lower Tier Min (??? this is the value I need - should return a 0). Sheet 2 - Column A - Cust, (61523), Column I - Volume From (0), Column J - Volume To (700,000), Column P - Current Tier (1), Column Q - Lower Tier (-), Column R - Higher Tier (2).

            1. On sheet one Column I is the amount for the tier that is in Q (this is the value I need returned on sheet 2). Sheet one has; Customer # in column A, and their tier level number on column Q.

              On sheet 2, Column A has the customer number. Column H has the tier level number.

              I need sheet 2, column I to tell me what the amount is on sheet 1 that matches both the customer number and the tier number.

              Sorry, I wish I could add screenshots. :)

              1. That is exactly what I needed! Thank you so much for all your help!! :)

  5. Hello
    i have 12 seet of 12 month and i want to make one master seet all seet have pary name and its payble amount , for exampale , we have to do 2000 payment to abcd party in jan. And 3000 in feb. How can i make master seet to seet all month payment in row, i want formula that put jan paybale ammount in jan cell ,not in diffrent month cell .
    So please help me with make this master seet
    Thank you.

  6. I have two Columns:

    Column A Column B

    OLF2-09-AI-001 1"-AI-130486-BAE3-IS50
    OLF2-09-AI-003 1/2"-AI-130586-A7A
    OLF2-09-AI-005 12"-AI-137584-AR3-IH25
    OLF2-09-BH-001 1"-BH-130486-A7A
    OLF2-09-BH-001 12"-BH-135846-A7A
    OLF2-09-BH-001 8"-BH-135874-A7A
    OLF2-09-BH-001 14"-BH-132145-A7A
    OLF2-09-GR-001 1"-GR1358100-AS3-IC
    OLF2-09-GR-001 1"-GR-130004-A7A
    OLF2-09-GR-001 1"-GR-130005-A7A
    OLF2-09-GR-001 1"-GR-1300001-A7A
    OLF2-09-NHD-004 1"-GR-136666-A7A

    I want to get result as value in column B against similar value of column A with largest number with " sign.

  7. Hi I am hoping to use excel to drive an advice action

    Column 2 = drop down list
    Column 3 = will return a text based action based on the response in Column 2

    For example:
    drop down = yes => Talk to Mel
    drop down = no => leave blank
    drop down = not sure => Talk to Sam
    drop down = [any other choice] => Further information required

  8. Hi, I have cell A1 Apples and Cell A2 Lemons on Sheet 2. Cell B1 and 2 is qty, now i have Cell A3 Apples & Lemons and Cell B3 qty. On my next Sheet
    I have Cell A1 Apples and Cell A2 Lemons. I want to vlookup the second sheet and count the qty over to Sheet 1, prob is i have Cell A3 with Apples and Lemons, but need to split count to sheet 1 ex: Apples and Lemons in Cell A3 qty is 4, now this must count to sheet 1 as apples 2 and lemons 2 and or apples =4 and lemons =4, either one of the 2 solutions will work as i have a database working with machinery. On my excel apples will be a machine (DD211) and lemons will be (DD2710) they use the same parts therefore sometimes I get 1 order for both machines, I still need separated data as to what was ordered for each, So it should be order 1 for DD2710 and DD211, 50 parts, on sheet 2 it must show DD211 1 order Cell A1 and cell A2 DD2710 1 order or it could shou DD211 25 parts Cell A1 and 25 Parts DD2710 cell A2, Hope i explained enough detail

    Thank you

    1. Hi!
      The VLOOKUP function cannot search for a match over a part of the text in a cell. Also, your problem is very confusing and cannot be solved by a single formula. If you have a specific question about the operation of a function or formula, I will try to answer it.

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

  10. 1) For Book A, I have 5 copies, For Book B, I have 10 copies, For Book C, I have 15 copies. I have 1000 books with different titles & quantity.
    2) I have to distribute these copies to different schools & also simultaneously maintain how many copies I have given to whom & how many are left.

    Please help me out.

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

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

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

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

        1. Hi!
          I don't quite understand what result you want to get. I can't see your data so I can't check the formula. Note that you are looking for text. The search data must also be text.

          1. Hi,
            If I put any Material number (which includes text and numbers) in A2 and then I want to know whether the material number which I am searching for is present in the other sheet or not by the result of "Y"OR "N" in the field V2.

            I know its hard to understand this without seeing the data, but just wanted to know which formulas I can use.

            Thanks for your help!!!

  15. 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";"")

    2. Hi!
      Please clarify your specific problem or provide additional information to understand what you need.

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

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

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

  19. suppose x employee OT done in jan-22 & claim in feb-22, again in mar-22 try to claim ot agst jan-22, then how to track/stop claim twice/double OT date in which excel formula to use

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

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

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

  23. I am trying to write a formula where if a cell = EBAY then take item number in column B and vlookup a range of data. Any ides?

    1. Hi!
      The information you provided is not enough to understand your case and give you any advice, sorry. Please describe your problem in more detail.

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

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

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

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

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

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

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

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

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

  33. Hello! may ask something, about vlook up, how can I automatically add new text in the table, then automatically apply in the advanced filter?

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

  35. A Cell - B Cell the answer is C cell. if B cell 0 Mean the Answer C Cell should get value for A Cell.

    My question is If B Cell 0 Value mean should C Cell also need get value 0.

    therefore, what is the function i need put in C Cell.

    please explain me

    1. Hi!
      I’m not sure I got you right since the description you provided is not entirely clear. However, it seems to me that the formula with IF function below will work for you:

      =IF(B1=0,0,A1-B1)

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

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

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

  39. Hello team,
    Currently I am working on a table in which I have to do the following:
    1st Check if specific cell A1has writen "OK" and if it is true then return "4", If not then lookup another cell's value B1 and return a value from array C2:F8 corresponding to the previous cell B2.
    The tricky part is that in the array from the lookup there are blanks which must be bypassed. If C3 is blank then use C4, if C4 is blank then use C5 etc.
    =IFERROR(IF(A1="OK",4,VLOOKUP(B1,C2:F8,??????)),"ERROR")
    I am unsure how to implement ISBLANK or if there is another solution

    1. Hello!
      And your explanation is not entirely clear what "corresponding to the previous cell B2" means. In addition, empty cells in the search column do not affect the result. Therefore, I don't really understand your problem. Maybe the VLOOKUP formula should be like this:

      =VLOOKUP(B1,C2:F8,B2,FALSE)

      If this is not what you wanted, please describe the problem in more detail.

  40. helped me a lot, thanks

  41. I want to make lookup and average at the same time.
    e.g.,
    "Sheet A" has data and time in one cell and i want to find that date and time exactly in "Sheet B".
    Once i find in "Sheet B" the exact date and time in next column i want to have average of last 15 values or cells. And this all i need to see in sheet one with 15 cell average.

    Please assist urgent.

    1. Hi!
      Unfortunately, your task cannot be solved using single Excel formula. I can't see your data, but I think you should use a VBA macro.

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

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

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

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

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

    2. Hello!
      Unfortunately, without seeing your data it is difficult to give you any advice. If you enter location data in cell A2, then in the formula in cell B2 you must use the data from A2. You can see examples of VLOOKUP in this article.
      If this is not what you wanted, please describe the problem in more detail.

  47. Please help!
    I have a template that I'm using a vlookup to populate the dollar value for the salary range's minimum, midpoint, and maximum. However, in the lookup table, if the job code doesn't have a salary range, the value appears as "1" for the minimum, midpoint, and maximum.

    I'd like the vlookup to return the value shown for the minimum, midpoint, and maximum, unless it equals "1", then I want it to displayed value to show "N/A" in the template.

    The following formula is correct, except it's missing the value shown in the lookup table when the value is greater than 1.

    =IF(VLOOKUP($F$9,'Active Job Codes List (25)'!$A$2:$Z$4168,14,FALSE)=1,"N/A")

    The following formula is correct, except it's missing the "N/A" if the value shown in the lookup table equals 1.

    =VLOOKUP($F$9,'Active Job Codes List (25)'!$A$2:$Z$4168,14,FALSE)

    Basically, I need to combine these two formulas and none of the options I've tried seem to work.

    Thank you!

    1. Hello!
      Based on your description, it is hard to completely understand your task. However, I’ll try to guess and offer you the following formula:

      =IF(VLOOKUP($F$9,’Active Job Codes List (25)’!$A$2:$Z$4168,14,FALSE)=1,”N/A”, VLOOKUP($F$9,’Active Job Codes List (25)’!$A$2:$Z$4168,14,FALSE))

      If this is not what you wanted, please describe the problem in more detail.

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

  49. Hello，=IF(HLOOKUP(J17,shopdirection,4,0)="L17","Y","N")，he output is either all yes or all No，why

    1. Hello!
      I can't see your data and I don't know what your formula should do. Try removing the quotes.

      =IF(HLOOKUP(J17,shopdirection,4,0)=L17,”Y”,”N”)

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

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