Comments on: Excel LOOKUP function with formula examples

There are a handful of different ways to look up and match in Excel. This tutorial discusses the LOOKUP function that is designed to handle the simplest cases of vertical and horizontal lookup. Continue reading →

Comments page 2. Total comments: 90

  1. Thanks for this! The simple fix of making sure my 'Lookup' data was in ascending order did the trick!

  2. I have a LOOKUP formula that works perfectly for my needs but is not dynamic:
    =LOOKUP(2,1/($F$2:$F$21=F2),$I$2:$I$21)
    Basically it finds the last occurrence of an item in column F of my table named AllData. It then sets the value of another column to the value found column I of my table.

    Works great except the lookup vector and the result vector do not expand with the table.

    How can I make this dynamic?

  3. Hi Svetlana,
    First of all thanks for the easy explanation, I'm not a professional in excel and I has to deal with a sheet with alot of equations and one of them is
    =lookups!$N$19
    Which I can't understand so could you please explain.
    Thanks again

  4. I have been unsuccessful with LOOKUP and IF sequence was too long.
    I have a table of texts, no values.
    Table contains Keywords to look for partial match and return Cause.
    Keywords Cause
    Arrival ARRIVAL/MISSORT
    COMM COMM - Research
    STAT[13] STAT13 - Research
    STAT[44] STAT44 - Research
    NAK NAK/OUTBOUND
    PUX[95] PUX95 - Retrieval/Unclaimed
    SIP SIP/NVAN
    blank DUPLICATE
    column C13 range contains the following and want Cause in column E13 range/selection
    Arrival@11/27 03:04
    COMM@11/27 16:48
    NAK@11/21 19:48
    PUX[95]@11/27 15:37
    SIPS@11/25 04:09
    STAT[44]@11/25 05:45
    Thank you for your time and help

  5. Dear sir/Mr
    vlookup all formula send to my email please

  6. In my company there are 3 shifts namely, A, B & C with timings 6:30 to 14:30; 14:30 to 22:30 & 22:30 to 6:30 (next day). While making a work report, timing inputs given by the my supervisors leads to confusion. For example, sometimes a supervisor puts timing 1:30(12 hours format) in A shift, whereas it has to be 13:30 (24 hours format). How to avoid such mistakes?

  7. Dear Reader, please help me i have on sheet Sheet1 and such data on sheet-like (A1=ram,A2=ram,A3=ram,A4=geeta,A5=geeta and A6=Geeta and same second column B1=blank,B2=blank,B3=value,B4=blank,B5=blank,B6=value.
    I want to apply the formula of vlookup where I want data based on ram & Geeta but vlookup fatch only first-row value of B1 & B4 which is blank but I need to prepare formula where vlookup pic only value row where B3 & B6.

  8. There is two column A and b.
    in a column 3 name repeated A1-Ram, A-2-ram and A-3-ram but column-B1- blank,B2 -blank and B3 is 5 i want last data by vlookup .

  9. DEAR MAM I HAVE TWO DIFFERENT WORK SHEET I WANT TO APPLY LOOKUP VALUE BASED ON ID FROM ONE SHEET TO ANOTHER ON THE BASIS I WANT TO FOUND THAT BASED ON ID QUANTITY ARE BOTH SHEET IS SAME OR DIFFERENT

  10. I want to get the information on the right side columns from another file which is updated already.

  11. Hi, im looking to add to my worksheet a drop down filter for example when i speak to customers and they want a specific item for example a car i want it to be able to hightlight the best fit for them ie price range, doors and so on so we can see if we have one in stock. is this possible.

  12. Hi Svetlana (and Ablebits team),

    no questions here, just a big thank you!
    Especially the part "Get a value associated with the last entry in a row" combined with the TIP to get the row number =LOOKUP(2,1/(A:A""),ROW(A:A)) was something I was unable to understand elsewhere ...although it actually worked also without me understanding why :)

  13. Hiii friend so v look up to use in computer

  14. Hi hope all are doing well
    I have some values distributed in a array from A1:AZ1230, I have an another sheet which has single column (A:A) & has all the values from A1:AW1230. I want to extract the relative value from AZ:AZ to B:B. is it possible? I have checked by lookup function but not succeed as lookup function is checking value in one column or row only. but here lookup value is distributed in many columns.
    Thanks

  15. Good day!
    Can I use LOOKUP to get the 1st occurrence of a specific value?

    Expected Output
    Code Code UT UT
    Code Start Code End UT Start UT End 1-Aug 2-Aug 3-Aug 4-Aug 5-Aug 6-Aug 7-Aug 8-Aug 9-Aug 10-Aug
    Object 1 3-Aug 7-Aug 8-Aug 10-Aug Code Code Code Code UT UT UT
    Object 2 1-Aug 5-Aug 6-Aug 9-Aug Code Code Code UT UT UT

  16. Good day!
    I want to use the formula =LOOKUP(2,1/(A:A""),ROW(A:A)) but it doesn't function with cells that have formula on it. What other formula should I use?

  17. hello friends pls help me with formula for below calculation..

    i have text in different column and i want last text value with criteria..

    for eg..text value in column...
    1-wrongnum,switchoff,callback,nc,contacted
    2-wrongnum,switchoff,contacted,nc,callback

    i want last text value in column where text is contacted -callback

  18. Hi,

    i don't understand the use of Look_up Value, which you have filled as 2. Can it be any number or it has to be 2 only.

    1. Hi Ashish,

      You are talking about the example that searches for a value in the last non-blank cell, right? If so, please read the "How this formula works" section carefully. It explains why we use a look_up value of 2.

  19. i have a spreadsheet with product code,product name, price and discount columns. the discount has 2 categories, sales and employees. They also gave the discount %s according to their different categories on a different sheet. They have asked me to find the discount amount based on the discount category. if there is no discount category the discount amount should be 0. PLZ HELP

  20. I have a spreadsheet and the value can be entered in any order in the rows which depends on calculated condition. My column remains same but row changes for these calculated values. I want to number the rows or find the rows in the order that it gets calculated. So for example row 1 might be calculated first, that should return me 1. Then may be row 3 gets populated so it should return 2. Then may be row gets calulated that should return 3. Is there a way to get this using formulas, if not using script which should calculate with out manual intervention.

    1. I have a spreadsheet and the value can be entered in any order in the rows which depends on calculated condition. My column remains same but row changes for these calculated values. I want to number the rows or find the rows in the order that it gets calculated. So for example row 1 might be calculated first, that should return me 1. Then may be row 3 gets populated so it should return 2. Then may be row 2 gets calulated that should return 3. Is there a way to get this using formulas, if not using script which should calculate with out manual intervention.

  21. I tried this:
    lookup(E30,{0,10,20,30},{($H$9+$F$14(E30-$F$9)),($H$10+$F$15*(E30-$F$10)),($H$11+$F$16*(E30-$F$11)),($H$12)})

    But I get an unspecified error message.
    I tried the above as a substitute for nested IF as below

    =IF(K10<F10,$H$9+$F$14*(K10-$F$9),IF(K10<$F$11,$H$10+$F$15*(K10-$F$10),IF(K10<$F$12,$H$11+$F$16*(K10-$F$11),$H$12)))

    Can you please explain. Thanks in advance.

  22. Hello,
    I have a data in following format :

    Format 1.
    -----------

    Name : xyz
    Address : xyz
    Area : xyz
    Contact : 123
    Contact Person : xyz

    Name : abc
    Address : xyz
    Area : xyz
    Contact : 234
    Contact Person : xyz

    ...

    Now the whole data consists of around 25000 rows with the same labels(i.e Name,Add,Contact,Contact Person) as above form in a single column,

    My query is how to copy all the data in following format column wise as follows:

    Format 2.
    -----------

    Name Address Area Contact ContactPerson
    xyz xyz xyz 123 xyz
    abc bcd agh 234 fgh
    ... ... ... ... ...

    Kindly, suggest me with a formula to list all the data from Format 1. to Format 2.

    Thanks and Regards,
    DKR

  23. PLEASE SEND ME AN ADVANCED EXCEL TUTORIAL

  24. Hi svetlana,

    I require your help for putting a excel formula in a single cell. Here I have 3 vertical column in which one is Inwards and seccond one is Outwards. In the third column, which represents previous cash balance amount, i need to put a formula which can deduct if the money goes outwards it should deduct it from balance or if the money comes inwards it should add with balance. I hope you will reply

  25. Good evening! I'm having issues with the following problem,

    In cell C24, enter a lookup function that looks up the value of cell B24, from the range named SurveyRange to supply a category letter in cell C24 based on the value in B24. The result should be an exact match. Use an appropriate mixed reference for the lookup value so the formula can be copied to cell D24.

    I've tried both HLookup, and VLookup. I've also tried other functions with no avail.

  26. Hii MM
    Please solve my problem.

  27. Hi Svetlana, I have a question, I could not solve this problem, So I need your help.
    I am working a private compny of HR Department.
    My problem is- 1-I have some STAFF OT data from 2014(january to December) to till year in sheet-1.
    2- I have some Operator OT data from 2014 (January to December)to till year in sheet-2.
    3- I have some Labour ot data from 2014(January to December) to till year in sheet-3. My table first column is-
    A2- Employee code.
    B2- Employee Name.
    C2- January
    D2- February like this upto December.
    If I will choose any category (Staff , Operator and Labour)with they're year then enter any Employee code number, automatically show that employee name and january to december OT data which category I had choosen with year.
    Please solve this problem

  28. Hi MM,

    Finally i solved it through index and match formula in a fraction of seconds. Thanks to the ablebits website.

  29. Hi MM,

    Let me try it.

  30. Hi MM,

    After inserting the ampersands, out of 5 false 3 disappeared 2 is still appearing. Above all, i added one more condition for application purpose. Now the formula is like

    IF($E3="APPLICATION",IF($F3="B","BNE-INRF-A",IF($F3="C","CONE-INRF-A",IF($F3="F","FBE-INRF-A",IF($F3="G","GCE-INRF-A",IF($F3="J","JVE-INRF-A"))))),"")&IF($E3="REGISTRATION",IF($F3="C",IF($G3="A","CONEI-A",IF($G3="B","CONEI-B",IF($G3="C","CONEI-C",IF($G3="D","CONEI-D"))))),"")&IF($E3="RENEWAL",IF($F3="C",IF($G3="A","CONGEN-E-A",IF($G3="B","CONGEN-E-B",IF($G3="C","CONGEN-E-C",IF($G3="D","CONGEN-E-D"))))),"")&IF($E3="REGISTRATION",IF($F3="B",IF($G3="A","BNEI-A",IF($G3="B","BNEI-B",IF($G3="C","BNEI-C",IF($G3="D","BNEI-D"))))),"")&IF($E3="RENEWAL",IF($F3="B",IF($G3="A","BNER-A",IF($G3="B","BNER-B",IF($G3="C","BNER-C",IF($G3="D","BNER-D"))))),"")&IF($E3="REGISTRATION",IF($F3="F",IF($G3="A","FBEI-A",IF($G3="B","FBEI-B",IF($G3="C","FBEI-C",IF($G3="D","FBEI-D"))))),"")&IF($E3="RENEWAL",IF($F3="F",IF($G3="A","FBER-A",IF($G3="B","FBER-B",IF($G3="C","FBER-C",IF($G3="D","FBER-D"))))),"")

    and the results are like this

    CONEI-DFALSEFALSE

    1. My fix was backwards. I avoided a negative result for the first check only. I think if the ,"" goes before the six parentheses it avoids the FALSE outputs.
      so instead of ))))),""), it should be ,")))))) at the end of each IF grouping.

      Have you considered concatenating your columns E,F,G into one string, and using that string to refer to a helper table?

      You could use lookup or the index-match of the concatenated value to find the proper product code, like so:

      "ApplicationB" results "BNE-INRF-A"
      "RenewalCC" results "CONGEN-E-C"

  31. Hi MM,

    Thanks for your reply. Its really worked out. But, still not perfect. But, helped lot.

  32. Hi, Svetlana, thanks for your post. I have learnt about the function of Lookup. Thank you.

  33. Instead mentioning contractor,foreign branch,local branch i mentioned only c or b or f in column B..

  34. Hi Svetlana,

    The formula for the above mentioned requirement is like below

    =IF($A1="REGISTRATION",IF($B1="C",IF($C1="A","CONEI-A",IF($C1="B","CONEI-B",IF($C1="C","CONEI-C",IF($C1="D","CONEI-D"))))))&IF($A1="RENEWAL",IF($B1="C",IF($C1="A","CONGEN-E-A",IF($C1="B","CONGEN-E-B",IF($C1="C","CONGEN-E-C",IF($C1="D","CONGEN-E-D"))))))&IF($A1="REGISTRATION",IF($B1="F",IF($C1="A","FBEI-A",IF($C1="B","FBEI-B",IF($C1="C","FBEI-C",IF($C1="D","FBEI-D"))))))&IF($A1="RENEWAL",IF($B1="F",IF($C1="A","FBER-A",IF($C1="B","FBER-B",IF($C1="C","FBER-C",IF($C1="D","FBER-D"))))))&IF($A1="REGISTRATION",IF($B1="B",IF($C1="A","BNEI-A",IF($C1="B","BNEI-B",IF($C1="C","BNEI-C",IF($C1="D","BNEI-D"))))))&IF($A1="RENEWAL",IF($B1="B",IF($C1="A","BNER-A",IF($C1="B","BNER-B",IF($C1="C","BNER-C",IF($C1="D","BNER-D"))))))

    and the answer is like below

    CONEI-AFALSEFALSEFALSEFALSEFALSE

    1. I believe the problem with the formula is the ampersands. What the formula ends up doing is concatenating each of the IF statements that test the $A1 value. You need to include a final negative condition as
      ,""
      in each grouping before the final parenthesis to avoid the "FALSE" output.

      For the quickest fix, CTRL-H and replace )))))) with ))))),"")

    2. Hello, Sivakumar,

      could you, please, send us the workbook with the data you used and the results you got at support@ablebits.com? It's hard to get a clear understanding from the comment, since the data is in a mess here.
      Thanks!

  35. Hi Svetlana,

    Thanks for your reply.
    I tried. But, i got answers for all the conditions. I want only one answer either it should be any one of the answers or else should be false.

    I am preparing the accounts of the non-profit organization i.e,it is a council. Their main activity is issuing new licence to engineers or renewal. But, there are 4 types of company they are handling 1.contractor 2.foreign branches 3.joint venture and 4.local engineers.

    Above all, types of engineers is 4, viz Category A,B,C & D. It is applicable to all types of above mentioned companies.

    For each type of company, registration and renewal there are some item codes available. I want to bring that item codes in the last column.

    My columns details are 1.purpose - registration or renewal 2.type of company - contractor,foreign brach,joint venture & local engineer 3.engineer type - A,B,C & D.

    So my last column should be any one of the item code, like it is below:-

    ITEM TYPE PURPOSE
    BNEI-A Registration
    BNEI-B Registration
    BNEI-C Registration
    BNEI-D Registration
    BNER-A Renewal
    BNER-B Renewal
    BNER-C Renewal
    BNER-D Renewal
    CONEI-A Registration
    CONEI-B Registration
    CONEI-C Registration
    CONEI-D Registration
    CONGEN-E-A Renewal
    CONGEN-E-B Renewal
    CONGEN-E-C Renewal
    CONGEN-E-D Renewal
    FBEI-A Registration
    FBEI-B Registration
    FBEI-C Registration
    FBEI-D Registration
    FBER-A Renewal
    FBER-B Renewal
    FBER-C Renewal
    FBER-D Renewal
    JVEI-A Registration
    JVEI-B Registration
    JVEI-C Registration
    JVEI-D Registration
    JVER-A Renewal
    JVER-B Renewal
    JVER-C Renewal
    JVER-D Renewal

  36. Hi Svetlana,

    Can we use multiple if conditions, in the above mentioned formula?
    I mean in lieu of checking the status column, want to check all the three columns by using if conditions. If the criteria in the column 1 is correct means,it has go to column 2 and should check and if the column 2 is also correct means it has go to the column 3 should display the results like Development, testing and completed.

    1. Hi Sivakumar,

      I believe you can use an IF formula with an embedded AND statement(s) like in these examples.

      If you are trying to achieve something different, please give me some more details about your task and we will try to work out a proper formula.

  37. Svetlana, the formula for the horizontal lookup needs fixed. It is the same as the vertical formula. The screen capture has it typed correctly.

    Thanks again for these helpful posts. I learn something new every time I visit!

    1. Good catch, thank you! Fixed. And thank you for your kind words about our blog!

  38. Hi Svetlana,

    Good morning!

    I want excel formula for remove duplicate and get unique values without any reference. Which is like remove duplicate option in excel function. Please suggest me...

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