Comments on: Advanced VLOOKUP in Excel: multiple, double, nested

In this VLOOKUP tutorial, you will find a number of advanced formula examples that demonstrate how to look up with multiple criteria, use two VLOOKUP functions in one formula, dynamically pull data from different sheets, and more. Continue reading →

Comments page 3. Total comments: 253

  1. How use in vlookup in two sheets

    1. Hi Anil,

      To do Vlookup from a different Excel sheet, you simply supply the sheet's name followed by an exclamation mark in the table_array argument, e.g.
      =VLOOKUP("text", Sheet2!A2:B15,2)

      For more information, please see How to Vlookup from another worksheet.

      If you want to perform sequential Vlookups in different sheets based on whether a previous Vlookup found the lookup value or not, you can nest Vlookup into IFERROR and use two or more such nested functions within one formula, like shown in this example: How to do sequential Vlookups in Excel.

  2. I need to lookup value of Product with No having latest date.

    Product No Date
    A 750000 14-09-2017
    A 85101 15-09-2017
    A 14413107 16-09-2017
    B 41351 14-09-2017
    B 1345654 15-09-2017
    B 1531546 16-09-2017

    1. Hello,

      if you find the formula in the article above a bit complicated or you'd like to get a quicker and simpler solution, please take a look at our Vlookup Wizard add-in. You will find it in Ultimate Suite collection that can be downloaded from this web-page. The add-in can be used instead of VLOOKUP function and will return the value you need in a couple of clicks.

      Hope it helps!

  3. Hi if I wana do an vlookup with an condition stating some specific word if that reflect the beside it only then the value in the column should be captured can someone help me with the it...???

    for example

    for 2 if satnam is present then the value should appear in from of two only specific value "Satnam" Note

    2-Satnam 12345 2
    3-Kiv 4567
    4-New 9756

    1. Hi if I wana do an vlookup with an condition stating some specific word if that reflect the beside it only then the value in the column should be captured can someone help me with the it...???

      for example

      for 2 serial number if satnam is present then the value should appear in front of two only specific value "Satnam"

      2-Satnam 12345
      3-Kiv 4567
      4-New 9756

      2

  4. Hi, i need ur assistance to get the result using formulas especially using vlookup. Example i have a large data. In a column i have a account numbers with different branches. Also few account numbers are same but different branches. Is it possible to get the result using vlookup with same account number with different branches from the large data. Please help me...

    And i would like to say thank you for providing the detailed functions of formulas. It is really helpful for me.

    1. Use
      =vlookup(a2&b2,data,3,false)

  5. Hi
    I'm trying to get all duplicate values in the lookup range from pivot table report into manual customised report.
    1. Pivot table worksheet have multiple duplicate names in the first column with various values in the eighth column.
    2. Summary worksheet have manual customised report layout with merged rows of all the names which are in the pivot table report.
    3.I would like to display all the values of those duplicate names from the pivot table to the manual summary worksheet report.
    Is that possible, please give me the right formula.
    I tried {=IFERROR(INDEX($K$7:$K$60,SMALL(IF($P$5=D7:D60,ROW(K7:K60),""),ROW()-3)),"")}, but it shows no values.
    please help.
    thanks in advance

    1. Try This ROWS($E15:E15)),2) instead of this ROW()-3)),"")
      ($E15:E15)) is a resulting value, where you want your result,,,
      If your answer value is in 2nd coloumn then type 2, if it is in 4th coloum then type 4 and close bracket

  6. Dear sir,
    If you please help me to solve the problem.

    I have
    (Sheet 1)
    A=1
    B=2
    C=2
    A=4
    B=5
    C=6
    I want(Sheet 2)
    Sum of A=?
    B=?
    C=?
    I Will be highly oblized to you.

    Regards
    Pappu Pattanayak

    1. Hi Pappu,
      You can use below simple command
      Think its in one sheet and result is also published in one sheet
      =SUMIFS($B$:$B$,$A$:$A$,D1)

  7. =VLOOKUP("Lemons",$A$2:$I$9,MATCH("Mar",$A$1:$I$1,0),FALSE)

    Working with this formula, but getting the #N/A error.

    Material Material Description 8/1 7/25 7/18
    105939 EBF Org Wild Arugula 2x2lb PL 574 1,350 21,321
    105940 EBF Org Baby Spin - 2x2lb PL 1,741 1,345 1,564
    2,315 2,695 22,885

    1. Need to lookup a material (105940) and bring the qty. on a specific date (7/18). Does this not work because it's trying to reference dates?

  8. Hi All,

    I have one question please some one help me to fix it.

    I have two tables one table is having only Serial numbers with removing duplicate
    Another table serial numbers like 10 serial numbers in one column with other details like products suppliers resellers etc.

    Here i want to perform vlookup for unique serial number table and get the supplier details to it.

    please help me some one. thanks

    1. Like this 10 serial numbers i have in some 250 and more than 250 characters length serial numbers column, i want supplier information from this sheet to the original sheet.

  9. great site......

    could you help me with the below query.....

    I am doing vlookup, my ref column will have duplicate but I need all their corresponding items in one single cell line by line... can u help me on this....

    1. Hello Sudhakar,

      Thank you for your feedback.

      I'm sorry, but we don't know of a simple way to get all values in one cell. You may need to use VBA for this task.

      You can get all values in different cells, please see the "Get all duplicate occurrences of the lookup value" section for a detailed description.

      Then you can use the Concatenate function to merge all the values you get into one cell. Please see this post for more information:
      https://www.ablebits.com/office-addins-blog/excel-concatenate-strings-cells-columns/

  10. I have 4 columns of Reg. No. Name Subject and Grade. I want to return the grade of specific subject of a student how can I use the vlookup formula.

  11. I have a large data for groups for e.g.

    GRP_1 GRP_2 GRP_3
    98465 5521 65466
    65468 6663 6541
    68465 6545 36541
    65466 8466 6541
    65466 9548 65666

    and I want to create a list of products and which group it belongs like

    Product Groups
    5521 ??
    6541 ??
    6541 ?
    6545
    6663
    8466
    9548
    36541
    65466
    65466
    65466
    65468
    65666
    68465
    98465

    Thanks

    1. Hello, Ashwin,

      Please try this formula:
      =IF(ISERROR(MATCH(E2, $A$2:$A$6, 0)), IF(ISERROR(MATCH(E2, $B$2:$B$6, 0)), IF(ISERROR(MATCH(E2, $C$2:$C$6, 0)), "",$C$1), $B$1), $A$1)

  12. Hey,

    I loved you instructions! I have one question.
    I am using INDEX MATCH to find a cell from another workbook and place the name of the cell near it. For example:

    =INDEX('[Schedule 11.30.15 Hotschedules.xlsx]Sheet1'!$A$8:$A$75,MATCH($B$10,'[Schedule 11.30.15 Hotschedules.xlsx]Sheet1'!$C$8:$C$75,0))

    Which works fine. But sometimes the "time" in the cell changes. For example $B$10 is "6:00 AM - 2:00 PM [Breads Sales]" but sometimes I use an employee that is only "6:00 AM - 1:30 PM [Breads Sales]". How can i make INDEX MATCH use 2 lookup values incase the first one fails?

    1. Hello Argenis,

      Try the IFERROR function that allows you to return another specified formula if the first one returns an error:

      =IFERROR(INDEX('[Schedule 11.30.15 Hotschedules.xlsx]Sheet1'!$A$8:$A$75,MATCH(_$B$10_,'[Schedule 11.30.15 Hotschedules.xlsx]Sheet1'!$C$8:$C$75,0)),

      INDEX('[Schedule 11.30.15 Hotschedules.xlsx]Sheet1'!$A$8:$A$75,MATCH(_$B$11_,'[Schedule 11.30.15 Hotschedules.xlsx]Sheet1'!$C$8:$C$75,0)))

      https://support.office.com/en-us/article/IFERROR-function-c526fd07-caeb-47b8-8bb6-63f3e417f611

      1. Thank you, It worked wonderfully!!!

  13. Below was the table my scope of search

    Customer Name Product

    Brown Apples
    HILL CHock
    Brown Sweets
    Acey Lollypop
    Wolf chikky
    Brown Biscuits
    Hill Alapino
    Wolf Jelly
    Hill gems

    Search Cell in the same Sheet like the following

    $G2 $H2

    Dan Brown Apples
    Sweets
    Biscuits

    $G$5

    Hill ????
    ????
    ?????

    wolf

    I had used the following formula to get the first set of search:
    =IFERROR(INDEX($C$2:$C$16,SMALL(IF($G$2=$B$2:$B$16,ROW($C$2:$C$16)-1,""),ROW()-1)),"")

    I like some help to expand (or) new formula to list all the duplicate values with respect to the string mention within the Left side, instead of hardcording the right side formula....that is $G$2

    Thanks for looking and trying to help me out...!!!

  14. hello,

    a have a question based on your example please:

    if a have the next situation:

    Dan Brown A B
    Dan Brown C D
    Jeremy Hill T I
    Dan Brown R T

    and I want to have the next result into another sheet :

    Dan Brown A B C D R T
    Jeremy Hill T I

    How can I do that?

  15. it seems that my formula isnt working ..i use your formula as refference but it always shows nothing.. anyone please help

    =IFERROR(INDEX(E5:E11,SMALL(IF(E2=E5:E11,ROW(F5:F11)-1,""),ROW()-3)),"")

    1. Lineth,

      Please make sure you press Ctrl + Shift + Enter to enter the array formula correctly.

  16. I am quite poor on excel and can do simple VLookups. I need to do one that I think should in corporate an If function but do not know how. I need it to Vlookup a selection of codes and return the rate from column 2. However for 1 code I need it to go to one list for the answer, for all other codes I need it to go to another list for the answer. e.g I have 10 carrier codes 9 of the codes have a fixed rate - easy but one of the codes has a different rate for every item. So for the 9 with a fixed rate easy however how do I tell it that if code is this odd one go lookup on this list but if not go lookup on that list? I have played around for days trying to work it out. hope that this makes sense

  17. If you send me some advanced excel formulas, I will be greatful....

    1. Hi SUBHAM,

      You can download a workbook with formula examples discussed in this article here.

      1. Thank you so mush its really helpful

  18. Hi,

    Thank you for your very useful posts and I hope you can help with a problem I have:

    I have a cell (C6) which contains text based on a vlookup formula. I want another cell (G6) to return either "True" or "False" based on the text in C6. In G6 I have 'IF(C6="Air Cooled","True","False") but this doesn't work. If I just type "Air Cooled" into C6 then it's fine, G6 returns "True".

    Can you please tell me if there is a way around this?

    Thank you.

    1. Hi Anne,

      Your formula is correct and it should work both for values typed manually and returned by other formulas, and it works just fine in my test sheet. An immediate reason for the formula not working that comes to mind is that the value returned by your Vlookup formula has some slight difference in spelling, or a double space between words, leading or trailing space, etc.

  19. I want 0 inspite of #N/A when ever vlookup comes up with no value (& there is also actually not any value against that respective reference).Can you kindly tell me how can I get that. I used If(iserror also but it brings "Blank" cell when there is #N/A but our requirement is 0.

    Help is greatly appreciated

    1. Hi Assad,

      You can use the IFNA function with 0 in the second argument, like this:
      =IFNA(VLOOKUP(), 0)

      If you want the formula to bring 0 instead of any errors, not only #N/A, then use the IFERROR function:
      =IFERROR(VLOOKUP(), 0)

  20. Team, i need code for below:

    Input:

    A 12
    A 11
    A 10
    B 11
    B 12

    Out put:
    A 12 11 10
    B 11 12

  21. Hi,

    I have a data of staff name, date and daily productivity.

    Data catagory as "Actuals"
    A B C D E
    Date Name Productivity % Agent Occupancy AHT
    05-Jan-15 Staff A 107 51.42 129
    06-Jan-15 Staff A 77 49.58 167
    07-Jan-15 Staff A 100 62.69 162
    05-Jan-15 Staff B 68 61.99 198
    06-Jan-15 Staff B 50 64.38 321
    07-Jan-15 Staff B 67 74.51 255
    05-Jan-15 Staff C 32 73.20 192
    06-Jan-15 Staff C 20 59.11 194
    07-Jan-15 Staff C 66 57.87 176

    How can i lookup for Staff A, C=B & C Performance as below?

    05-Jan-15 06-Jan-15 07-Jan-15

    Productivity
    Staff A =VLOOKUP("Staff A",Actuals,MATCH(05-Jan-15,Actuals,0),FALSE)
    Staff B
    Staff C

    % Agent Occupancy
    Staff A
    Staff B
    Staff C

    AHT
    Staff A
    Staff B
    Staff C

    The formula seem doesn't work. May you assist?

    Thanks,
    Jenny

    1. Instead of using lookup formula you can use Sumproduct function...

      As per your info suppose you want to see the performance of Staff A for 5th Jan 2015...

      So as per your column format the formula would be

      Productivity

      =SUMPRODUCT(--(TEXT($A$2:$A$11,"d-mmm-yy")="5-Jan-15")*($B$2:$B$11="Staff A")*($C$2:$C$11))

      % Agent Occupancy

      =SUMPRODUCT(--(TEXT($A$2:$A$11,"d-mmm-yy")="5-Jan-15")*($B$2:$B$11="Staff A")*($D$2:$D$11))

      AHT

      =SUMPRODUCT(--(TEXT($A$2:$A$11,"d-mmm-yy")="5-Jan-15")*($B$2:$B$11="Staff A")*($E$2:$E$11))

      Please check...

  22. Hi,

    I have a series of data.

    Category No. EBT 53,483
    1 Computed expected tax 18,719
    2 State taxes, net of federal effect 469
    3 "Indefinitely invested earnings of
    foreign subsidiaries" -4,744
    4 Research and development credit, net -88
    11 Domestic production activities deduction -495
    11 Other 112
    Provision for income taxes 13,973

    I need vlookup to find the categories 11, add both the ocrresponsing numbers (i.e. -495 and 112) and present it in the cell.

    Can i do this. I tried =SUM(VLOOKUP(lookup value, lookup range, {2,3}, FALSE)) but it doesnt work as the numbers are 1 blow the other.

    Its urgent... can some1 plz help.

    Thx

    1. you can use the sumproduct formula...Suppose Column A contains Category number and columns B contains the corresponding number ...so to find the added value for category 11 you can use the below one...

      =Sumproduct((A2:A12=11)*(B2:B12))

      Please check and confirm...

  23. imagine there are several numbers from 1 to N, and each number can be repeated n times. each number has a specific price. by entering a number from 1 to N How is it possible to get the lowest price from the data table. for example :

    Numbers Price
    1 300
    3 400
    7 700
    8 650
    6 300
    5 200
    1 150
    3 400
    7 210
    7 340

    Now by entering 7 we want to find the lowest price which is 210.
    how is it possible?? please help me . thank you in advance.

    1. Hi Alireza..

      If you can sort the data by ascending order then I guess it would be possible by below method..

      Suppose You have column from B2 to B11 the number from 1 to N and its corresponding column (C) contains the specific prices..Then sort the data from largest to smallest at column C then by Column B..After that use the below array formula ..

      =(INDEX($C$2:$C$11,SMALL(IF(7=$B$2:$B$11,ROW($C$2:$C$11)-1,""),1)))

      Please do not forget to press Cntl+Shift+enter after putting the formula..

      Please let me know if it is working or not... In the mean time I will be looking for another method...

      1. Thank you so much for your swift answer. I will try it and I will let you know.

  24. Hi Svetlana,

    This is one of the best technical Excel posts I have ever had the pleasure to read. Thank you and well done! I do have a question however, what precisely are the "Row" functions doing in the array formula? I am having a hard time unpacking what is going on there... I understand the syntax but not the context. Would you please break that down a little bit more?

    Lastly, in your example: "How to get all duplicate values in the lookup range", how would you re-write the formula to report the purchased products next to the customer name, starting in cell G2 and continuing on to the right to cell J2, etc?

    Thank you for your help!

    -Will

    1. Hi Will,

      Thank you for your kind words and a great question : )

      I thought other readers might want to know the details too, so I've added the formula explanation to the post, hopefully it will be helpful.

      And here's the formula "to report the purchased products next to the customer name, starting in cell G2 and continuing on to the right to cell J2":

      =IFERROR(INDEX($C$2:$C$16, SMALL(IF($F$2=$B$2:$B$16, ROW($C$2:$C$16)-1,""), COLUMN()-6)),"")

  25. i want sheet to sheet multiply total formula .. excell sheet

    1. Hi Prem,

      It is difficult to recommend anything based on such generic description. Most likely you need to use the SUMPRODUCT function.

  26. Hi, I have a workbook with multiple tabs. In my master sheet I have values in column A (AA, BB, CC, DD, EE....for example). Then I have tabs labeled AA, BB, CC, DD, EE. I'm trying to do the same vlookup but on different tabs depending on what my master sheet column A value has...

    So if Column A is BB I want the vlookup to look at sheet BB. Here is the manual way of doing it:

    =VLOOKUP(B1,BB!A:B,2,FALSE)

    Here is what i want the formula mimic so it works in a similar fashion:

    =VLOOKUP(B2,A2&"!A:B",2,FALSE)

    I also tried giving A:B on sheet BB a reference of 'BB' in hopes this would work:

    =VLOOKUP(B2,A2,2,FALSE)

    Any help is appreciated!!!

    1. I need to do the same sort of thing as Luke.

      I Have a cell lets say its A1 that specifies from a drop-down list a Sheet Name (AA BB CC DD etc)
      I want to pass that reference to a VLOOKUP Formula which would be SOMETHING LIKE =VLOOKUP(B5,A1&!D1:E22,2,False)
      Where B5 is the cell containing theLookup_value, A1 is the cell containg the sheet name, and D1:E22 is the Table array.

      I have been told the INDIRECT formula should work but I am jet to find a way to get a successful result.

      Anyone Know?

  27. I have the following
    A 2
    B 5
    C 3

    I want it to
    A
    A
    B
    B
    B
    B
    B
    C
    C
    C

    Can you help me with excel formula

    1. Hello Daro,

      A special VBA script will be the best solution to your task. Sorry, I cannot help with this.

  28. I need a formula were in can get what ever the data i feed in from sheet 1 to sheet 2 in the same workbook ?

    1. Hello Samantha,

      If you need to simply have the data copied from sheet1 to sheet2 as you enter it, you can put this formula in cell A1 on sheet2 and then copy it to all other cells:
      =Sheet1!A1

  29. Dear Sir,

    Iam trying to do lookup with multiple cateria like

    I have one sheet with account number,name & business

    as well I have one sheet, when I will mentioned account number in sheet one formula will check aacount number & name then business will come

    1. Hello Sandeep,

      A similar example is described in How to do a vlookup with multiple criteria. This example explains how to look up with 2 criteria "Customer Name" and "Product". Your can download the example in the end of that section and adjust the cell references in the formulas.

  30. Dear Svetlana,

    I am very much impressed by the guidelines you are providing for the problems faced by the Excel users. Really it is a quite best Website for having expert advice for advanced Excel users.

    Thanks for your sharing.

    With regards.

    CMA Anand

    1. Thank you very much for your kind words, Anand! I'm pleased to know you've found the tutorial helpful.

  31. I believe this is the info i've been looking for to put together this project that i've been trying to wrap my head around
    What i would like to do is Input a "part#" using VLOOKUP to list all of the tools I need to make said part (i've accomplished this much) at the same time have it kick back a number that refers to current inventory for said tools (this number can change at any moment). So i know i'm going to need more than 1 spreadsheet to complete this task. I can run an inventory report anytime and export it to excel so my question is, can i build a template that i can drop my current inventory list in to that already has the formulas written out? What are the formulas i need to use to tie the 2 spreadsheets together? We have hundreds of different part numbers and thousands of tools and not having a system in place has bogged us down. End goal is to avoid "spot buying" and get ahead of the curve and forecast my tool ordering before the job hits the floor. Tool list's with the part #'s are already populated awaiting my completion of this task
    Thank you much and i hope theres no confusion here.

    1. EXAMPLE
      Input:866637
      Return: 1/4 drill 4pcs
      .3438 drill 2pcs
      3/4 end mill 1pc
      1/2-14 npsf 5pcs

      i figured one workbook would be my part numbers with tools listed and the 2nd being my current inventory.

  32. Hi,
    I need to get the PR number in Sheet: PO Short Table from Sheet : Pivot Table, based on Drawing ID and Material. Basically lookup & match 2 cells(A3,B3) and get the value from C3.
    Which formula to use? Pls help.

    Sheet : Pivot Table
    Sum of PR Qty.
    Drawing ID Material PR No. Total
    LMV-41105060 SDSU16404121 3000053435 13

    Sheet: PO Short Table
    DWG ID System Matl No Matl Description PO Qty PR No
    LMV-41105060 AFS SDSU16404121 STUD PAD 0

  33. I am creating a new sales analysis file to track sales over several years. My file has multiple worksheets. The first sheet, Sheet1, has raw data downloaded from our order entry system. Column D in Sheet1 holds the invoice date.

    Because we run on a Fiscal year cycle, not a calendar year cycle, I now want to automatically calculate the correct fiscal year for the invoice date into Sheet1, Column F.

    I have created a table on Sheet2 which holds date ranges that corresponds to fiscal years, ie:
    Sheet2, Column A, Rows 1 thru' 10 = StartDate
    Sheet2, Column B, Rows 1 thru' 10 = End Date
    Sheet2, Column C, Rows 1 thru' 10 = Fiscal Year

    I have been struggling to find a formula that will automatically calculate this information for me on Sheet1 in Column F. I have tried index-match, vlookup, lookup, LessThan, etc and cannot get this figured out.

    Any help would be greatly appreciated. Thank you.

    1. Hello Pam,

      On Sheet 2, sort the dates by column A in ascending order, and then you can apply MATCH with the "less than" match_type parameter (1 or ommitted):
      =INDEX(Sheet2!$B$1:$B$10,MATCH(D2, Sheet2!$A$1:$A$5,1))

      1. Svetlana - Thank you for the reply. I will definitely give that a try today! Your help is greatly appreciated. (Sorry for my slow reply back - I have been out of the office this past week with the 'flu and just came back today).

  34. I am looking to do a lookup on based on two criteria, where one of the criteria would be based on a range rather than an exact match. Do you know how I can do that?

    Here are the 2 criteria:
    Lumen
    Output Code
    2170 32.F
    4970 32.F
    4971 42.CF
    1185 15.CF
    1407 32.F
    1185 32.F
    1086 32.F

    The codes are an exact match, but the lumen output would fall between the following ranges, so for example, I'd want 2170 to return 18. I used the following formula to find a match based on the 2 criteria but it only works if it's exact: =LOOKUP(2,1/(B3:B13=C16)/(D3:D13=C18),(C3:C13))

    Lumen
    Output Wattage
    1350 9
    1800 12
    1800 12
    2100 14
    2700 18
    3300 22
    3300 22
    4970 28
    5400 36
    6600 44

    Thank you in advance,
    Andrea

    1. Thank you so much. And I can use this formula to match multiple criteria:

      {=INDEX(C3:C13,MATCH(1,(B3:B13=C16)*(D3:D13=C18)*(E3:E13=C18),0))}

  35. Hi!
    I'm trying to use your formula which I have put together as follow:
    =IFERROR(INDEX(RFVDTL!$F$2:$F$1757, SMALL(IF($D$2=RFVDTL!A2:A1757,ROW(RFVDTL!F2:F1757)-1,""), ROW()-3)),"")

    However, when I press Ctrl + Shift + Enter, I get an error s which is "Array Formulas are not valid in merged cell" can you help please?

    1. Hi!

      The point is that you are trying to apply an array formula to merged cells, which is not possible.

      You can either unmerge the cells or enter an array formula into any other non-merged cell. If you choose the latter, then type =X20 in your merged cell, where X20 is the cell that contains an array formula.

  36. I am trying to get all duplicate values in the lookup range, but I need help because some of the names on the left only contain part of the name. For example, I want to find all the part numbers for any "gold" material. So the names on the left could be:

    Gold rock 123
    Solid gold chair 234
    Silver and gold frame 567

    So could I put in "gold", and have it produce all three of those part numbers? instead of an exact match? I hope this makes sense.

    Thanks

    1. Hello Kat,

      You need to add a helper column to your source table and enter the following formula there:
      =IF(ISERR(SEARCH("gold",A2))=FALSE,"OK","")

      Where A is the column with the original text.

      Then in the master table, search for all "OK" instances in your helper column using formulas and pull out the corresponding Part numbers.

  37. With this formula in F4 -- {=IFERROR(INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3)),"")}
    I get "Apples".

    How do I get the information on the remaining rows to show up in F5 etc..

    Thanks in advance for any help.

    1. Hello Ann,

      Simply copy the below formula down to a few more cells, e.g. F4:F8, as in the described example. The number of cells where you copy the formula should be equal to or larger than the maximum number of entries the formula may return.

  38. can you help me work out this formula

    I need to search number that can be more than 1 = SHEET 2
    then if it is more that 1 word, I need to add them

    have the number in SHEET 1 = need the output "apple / orange / grapes" for 293
    search the number and fruits in SHEET 2

    example
    293 apple
    293 orange
    293 grapes
    294 mango
    294 avocado

    need output "apple / orange / grapes" for 293
    need output "mango / avocado" for 293

  39. Hi,

    Thanks for the helpful information.

    I'm hoping you can point me in the right direction for a project I'm working on.

    I have a table or list of values which I need to search for in an excel worksheet. So for example, the list would be hammer, nails, screws, bricks, etc. And the text would be...in cell A1 "You should have 25 bricks, 10 penny nails and a rubber hammer to complete the project." In cell A2, "Begin by laying out the bricks"...So what I need to do is to write a macro to go through the list, item by item and COUNTIF I get a hit within a range of text, A1:D45. In this case bricks are mentioned 2x, nails 1x and hammer 1x. Also, the length of both the incoming list and the text I'm looking through will be variable. Does that make sense?

    Many thanks,

    1. Hi Bonnie,

      In theory, you can fulfill this task using formulas, but a more flexible and quick way would be to use a VBA macro. You can search for it in special VBA sections on these forums: excelforum and mrexcel.com.

      1. I am having a sheet with names in one column, and in another sheet with names and numbers. i have used vlookup to get the number from sheet 2 to appropriate value in sheet 1. The difficulty i am facing is in sheet 2 same names are there for different numbers, so vlookup is giving the first match value and leaving the rest. Help me to solve this issue.
        like for the name glass there may be 100 101 102 456..numbers, if i am comparing glass from sheet 1 to sheet 2 it is just picking 100 for all the rest of the names (having glass in sheet 1).

  40. Hi Svetlana, I am facing issue in V lookup, as I want the 2nd 3rd or 4th lookup value in a different tab but also I dont want to add a helper column. Can you please suggest how could I do that? Your help is appreciated.

    1. Hi Roshan,

      For the 2nd lookup you can use the following formula (described in the article):
      =IFERROR(VLOOKUP($F$2,INDIRECT("$B$"&(MATCH($F$2,Table4[Customer Name],0)+2)&":$C16"),2,FALSE),"")

      You can modify it for the 3rd and 4th lookups, but these will be very long, complicated and slow formulas. That's why I'd rather go with a helper column and then hide it.

  41. Please fix the section title:

    Use VLOOKUP and INDERECT to dynamically pull data from different sheets

    It should be INDIRECT

    I respect your site very much. It's just that spelling errors are a pet peeve of mine.

    1. Hi Rick,

      Thank you very much for spotting this error, fixed! My spell checker ignores capitalized words, and here it is : (

      1. Very useful site, was looking over a formula from past 2 - 3 days, finally got it here with possible result which i was expecting for. Will surely mail you if any help required in future

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