LOOKUP where search string appears multiple times

{ 244 Comments }

By Ashish Mathur · · , · Leave a comment

In a list with multiple occurrences, the VLOOKUP() function in Excel will only return the first occurrence of the string being searched.

Depending upon the version of MS Excel which you are using, there could be two ways to solve this problem

Solution for MS Excel 2010 and higher versions

If you are using the Power Query add-in, then a few simple steps (no formulas at all) can solve this problem.  The result will be dynamic and refreshable (just as in a Pivot Table)

You may watch a short video of my solution here

Solution for all versions of MS Excel

While this solution works for all versions of MS Excel, it uses an array formula (Ctrl+Shift+Enter).  Array formulas, if used extensively in the workbook, adversely effect the system’s performance.

I  have presented three cases in the this workbook.

You may refer to my explanation of the array formula here.

Leave a Comment

Your email address will not be published.

CAPTCHA

*

  • Say the list comprised three different Ashish persons, e.g different family names, but the searcher searched only on the string ‘Ashish’, how would the formula return all instances of Ashish. My example would be say, Ashish Pongal, Ashish Ashish, and Ashish Sanjay. If you search on Ashish, get back all three occurrences.

    Reply

    • So in this instance, if you don’t mind using an extra column, use concatenate function to add first and last name, and in the vlookup formula, use concatenate to find the exact match.

      something like: vlookup(concatenate(first name column, second name column), $Column1:$column2,(enter the concatenated column number in the table), false)

      Reply

  • In the example files that you give, could you set the search routine so that if the name cells contained both forename and city to distinguish each different ‘Ashish’ that searching by forename alone, Ashish, the routine will return all four instances of Ashish with the full cell content: say you had Ashish Bangalore, Ashish Delhi, Ashish Chennai, Ashish Bangalore could you set the routine to find all occurrences of Ashish and return the full entry just searching on ‘Ashish’?

    Reply

    • Hi,

      Try this

      1. Assume your data is in range B4:B8
      2. Enter your search string in cell B12
      3. In cell C12, enter the following array formula (Ctrl+Shift+Enter) and copy down

      =IFERROR(INDEX($B$4:$B$8,SMALL(IF(ISNUMBER(SEARCH($B$12,$B$4:$B$8)),ROW($B$4:$B$8)-ROW($B$3)),ROW(1:1)),1),"")

      Hope this helps.

      Reply

      • Dear Sir,
        i am using the following formula to get the corresponding detail of a certain cell, but in excel while generating the data is take lot of time “Calculating processor %%%”, is their any alternative like VBA etc to generate the data, please help
        =IFERROR(INDEX(updating..!$A$2:$K$31311,SMALL(IF($C$6=updating..!$A$2:$A$31311,ROW(updating..!$A$2:$A$31311)-MIN(ROW(updating..!$A$2:$A$31311))+1,””),ROW(updating..!K1)),COLUMN(updating..!K1)),” “)

        Regards,
        Saviour

        Reply

        • Hi,

          Please share data and your question – describe your question. Also, share your expected solution. Upload your workbook to OneDrive and share the link of the workbook here.

          Reply

      • Hi Ashish,

        Is this formula limited to a small set of data as i tried putting it for big data and it didnt give any result, however it did give perfect results for a small set of data.

        Reply

        • Hi,

          The formula should work irrespective of the number of rows. Ensure that the ranges specified in the formula are correct. Also, since it is an array formula, you have to confirm the formula with ctrl+Shift+Enter.

          Reply

      • Hi Ashish,
        What if I wanted to lookup and return the multiple occurrences separated by commas in just one cell? What would the formula look like? In my case I search for text, not numbers.
        Do you know if that also could be achieved using DAX formulas in POWERPIVOT?
        Thanks.
        Sony

        Reply

        • Hi,

          Yes, this can be done in Excel 2016. In this version, if you have an Office365 subscription, you will get access to a few new Excel functions such as TEXTJOIN() and CONCAT(). These functions will allow you to accomplish your objective. This is also possible in PowerPivot available with Excel 2016.

          Reply

  • Thank you. THis solved the problem completely. It would be great if you could do another paper to explain the logic behind this formula. I am simply using it without understanding what each part does, particularly the -(ROW$B$3) part.
    Many thanks for your help

    Reply

    • You are welcome. The -(ROW$B$3) part is simply substracting the first row of data from ROW($B$4:$B$8) thus creating a virtual array of consective positive numbes from 1 to 7.

      Reply

  • Hi,

    Here you have mentioned source with same name but I’m having different sources in which some text string is common. So based on that I need to get some data.

    I’ll state my question here & the attachment is in Mera windows. Kindly fetch that file from there…

    Question…
    * Cloumn A contains some Messages received by a system.
    * In Column B I need those messages starts with “ETS FLD”.
    * In Column C I need those messages starts with “ETS DGR”.
    * In Column D I need those messages starts with “ETS GSM”.
    * In Column E I need those messages starts with “ETS CDMA”.
    * In Column F I need rest other messages.

    Reply

      • Hi,

        Thks for your reply. But I couldn’t get the result. I tried with the below mentioned formulae but still shows blank cell when I give a Ctrl+Enter…

        =IFERROR(INDEX($A$2:$A$706,SMALL(IF(ISNUMBER(SEARCH("ETS FLD",$A2)),ROW($A$2:$A$708)-ROW($A$1)),ROW($A$1)),1),"")

        Request you to work on my data & give a result.

        Reply

  • Hi,

    Try this array formula (Ctrl+Shift+Enter)

    =IFERROR(INDEX($A$2:$A$706,SMALL(IF(ISNUMBER(SEARCH("ETS FLD",$A$2:$A$706)),ROW($A$2:$A$706)-ROW($A$1)),ROW(1:1)),1),"")

    Reply

  • Hi,

    I have used the lookup formula to get multiple values for duplicate search strings in my cricket stats spreadsheet. The question I have now is how can I modify this formula {=INDEX($B$2:$C$113,SMALL(IF($B$2:$B$113=$N$2,ROW($B$2:$B$113)-ROW($C$1)),ROW(B1:C1)),2)} so that it returns the highest value?

    Thanks
    Bhagyesh

    Reply

    • Hi,

      One easy way would be to use the MAX function on the values which you get from the formula you have used. However, if you wish to get the maximum value via one formula, then try this array formula (Ctrl+Shift+Enter)

      =MAX(IF(($B$2:$B$113=$N$2),$C$2:$C$113))

      Hope this helps.

      Reply

      • Hi,

        Thanks for the reply. I tried the formula you gave, but it doesn’t work, just returns 0.

        Thanks
        Bhagyesh

        Reply

        • Hi,

          I tried it on my sample data and it worked just fine. Check for the following:

          1. The spelling in cell N2 should match the spelling with the entries in range B2:B113; and
          2. The values in range C2:C113 should be numbers (not numbers stored as text). To verify this, in cell D2, enter =ISNUMBER(C2) and copy till cell D113. If all entries are numbers in range C2:C113, values in range D2:D113 should be TRUE.

          Please check and revert.

          Reply

  • Hello,
    I am using your original formula (=INDEX(A21:F23,SMALL(IF(A22:A23=A28,ROW(A22:A23)), ROW(1:1)), 2) and trying to return multiple values for all items listed as “Open” on multiple tabs. I’m starting with the first tab of data (labelled Sports) and the formula keeps producing a Value error. Can you take a look at the data and see where I’m going wrong?

    Reply

  • Hello,
    The formula is working beautifully now – I played around with it a little and it’s great. I do have an issue, however… I have multiple tabs within my worksheet. On my current tab, I’m trying to consolidate all of the “Open” items displayed on the various sheets. If my current tab indicates a particular brand ie Style and I only want the formula to search for Open items within the Style tab, how can I do this? I’m thinking there must be a nested IF function within your formula. If I have tabs labelled Style, Tech, Sports, how do I add to your formula so that only data in the relevant tabs are searched for?

    Reply

    • Hi,

      Assume the following setup

      1. Let’s assume that the tab where you are consolidating all information is called All Brands. On this tab, one may select any brand from a drop down list in column A. The brand selected here should be the same as one of your worksheet names. Headings are in row 1
      2. In column B of the All Brands sheet, one may select Open or Closed from a drop down list
      3. On all other tabs, Open and Closed are available in column C and Item descriptions are in column A. Headings are in row 1
      4. Spelling and order of headings on all sheets (including All Brands sheet) are the same
      5. In cell C2 of All Brands sheet, try this array formula (Ctrl+Shift+Enter)

      =INDEX(INDIRECT("’"&A2&"’!$A$2:$F$416"),SMALL(IF(INDIRECT("’"&A2&"’!$C$2:$C$416")=$B2,ROW(INDIRECT("’"&A2&"’!$A$2:$A$416"))-ROW(‘NBC Sports’!$B$1)),SUMPRODUCT((B$2:B2=B2)*(A$2:A2=A2))),MATCH(C$1,Style!$A$1:$F$1,0))

      If you are pasting this formula in your file, please replace curly single quotes with straight single quotes.

      Reply

  • Thanks Ashish! I just tried it and it works beautifully!! If I want to add other Open items from additional tabs (ie E, News, etc), how should I modify the formula. I tried it with Sports and Style and it works, but it I want to add E or any other tab, how would I go about doing it? Thank you so much! 🙂

    Reply

  • Actually, I just tried your formula with different brands and it works!! You are amazing, again. Are you based in LA? Would love for you to give a presentation for our company.

    Reply

  • Hi Ashish,
    One last question.

    Let’s say in my All Brands tab, in addition to populating the Open Items column (C), I also want to populate Columns D-H (Status/Note, Owners, Date Updated and Priority), with data from the various tabs.

    How would I modify your formula so that in addition to populating Items from multiple ranges, I can also populate other columns?

    Reply

    • Hi,

      You simply need to copy the formula to your right. The MATCH(C$1,Style!$A$1:$F$1,0) portion of the formula reads the heading in row 1 and determines the column to pick up the data from.

      Reply

  • Hi Ashish,
    I have one more question that requires your expertise! 🙂

    I have transferred your formula to another doc and using it there. It works beautifully, but the only thing I’m having trouble with is adding an AND condition. Please see below:

    1. Let’s say I have multiple brand tabs (Bravo, NBC, USA, Oxy, Syfy) with consistent header rows (Series, Ep#, Air Date, etc).
    2. I am currently working on a tab titled ‘All Brands’ in which I want to consolidate data from the multiple tabs
    3. Col A in my ‘All Brands’ tab has the network name (which exactly matches the tab name), column B has my Flight Start, column C has my Flight End. In Column D (‘All Brands’), I am looking to pull all series indicated in the brand (col A) that has an Air Date (col F in the individual brand tabs) that fit between the flight start and flight end (indicated in Col B & C of ‘All Brands). To do this, I want to slightly modify your formula and add an And condition –
    If there are series whose air date is greater than or equal to the Flight Start and less than or equal to the Flight End indicated in ‘All Brands’, place here.
    Here’s what I tried and it’s not working:

    =INDEX(INDIRECT(“‘”&A2&”‘!$A$2:$J$416”),SMALL(IF(AND(INDIRECT(“‘”&A2&”‘!$F$2:$F$416”)>=$B2,(INDIRECT(“‘”&A2&”‘!$F$2:$F$416”)<=C2)), ROW(INDIRECT("'"&A2&"'!$A$2:$A$416"))-ROW(NBC!$B$1)),SUMPRODUCT((B$2:B2=B2)*(A$2:A2=A2))),MATCH(D$1,USA!$A$1:$J$1,0))

    Reply

    • Hi,

      Try this array formula (Ctrl+Shift+Enter) in cell D2 of All Brands sheet. You may copy this down and to the right.

      =INDEX(INDIRECT("’"&$A2&"’!$A$2:$J$416"),SMALL(IF(INDIRECT("’"&$A2&"’!$F$2:$F$416")>=$B2,IF(INDIRECT("’"&$A2&"’!$F$2:$F$416")<=$C2,ROW(INDIRECT("'"&$A2&"'!$A$2:$A$416"))-ROW(NBC!$B$1))),SUMPRODUCT((B$2:B2=B2)*($A$2:$A2=$A2))),MATCH(D$1,USA!$A$1:$J$1,0)) Hope this helps.

      Reply

  • Ashish,
    As always, you are amazing! The formula worked beautifully – thank you again. How did you learn Excel? I am taking a formulas and functions course, but haven’t gotten as advanced as you obviously are. I’m familiar with the Index, Indirect and Row functions, but not necessarily how you have used them here.

    Reply

  • The Linked file http://sdrv.ms/Ra14Le has
    two sheets: one is data sheet, another is Search Result Sheet. What I
    want is I will type a subject Code in A1 of Search Result sheet and
    the ROLLs (in Data sheet) related to this will be filled down in A
    column of Search Result sheet. In this way I will put other codes in B, C, D……..

    I have given two examples in Search Result sheet.
    Please frame the Formula in such a way that I can use it in other contexts.

    Regards
    Haque

    Reply

    • Hi,

      You may refer to my solution in the worksheet named Ashish’s solution at the following link.

      This is an array formula based solution. Please read my note in the blue box on the worksheet named Ashish’s solution.

      If the solution is cumbersome in terms of taking time to process, then post back. I will design a solution using VBA and Advanced Filters.

      Hope this helps.

      Reply

    • Hi,

      In cell L2, enter this array formula (Ctrl+Shift+Enter) and copy down

      =IFERROR(INDEX($G$3:$G$14,SMALL(IF($A$3:$A$14=LOOKUP(2,1/(K$3:K3<>""),$A$3:$A$14),ROW($A$3:$A$14)-ROW($A$2)),ROW(1:1)),1),"")

      Reply

  • hi,
    in summary – the excel file has 3 worksheets
    1.worksheet 1 is the main player report
    2.worksheet 2 is the expected plays – what the players were expected to play – which games and what the score was..each player can play several games
    3.worksheet is the actual plays – what the players actually played – which games and what scores ..again each player played several games –that were different from expected.

    Desired Result:
    1.I have in the main player report the list of constants that exist in both expected and actual which are: below;
    PLAYER’S NAME PLAYER ID POSITION COACH NAME TEAM
    The rest I would like extracted from each worksheet.

    Desired Result for extraction from each worksheet:
    Entries to Appear in Individual Cells:
    (a) I directly want all entries to appear in individual cells instead of getting them first into one and then splitting them.
    Desired Result for sum
    Total Sum of Each Player’s Scores Listed (Total Sum for Expected/Actual Score per player)
    (b) total sum of each player’ actual game scores and total sum of each player’s expected scores listed. I directly want all expected sum and actual sum entries players totals to appear. In other words I want to know how much each player’s expected score was and actual score was per game. This will help give awards based on scores and assess which future games to play.
    Desired Result for game name and scores to appear
    2.I would like expected game and score extracted from the expected worksheet for each player

    3.Also, I would like the actual game and score extracted from the actual worksheet for each player
    Desired Result for game names to identified correctly
    4.Each player will have the expected games and scores and the actual games and scores
    (a) For the games, Los Angeles and St. Catharines is one game respectively. Currently, they are separated. They are Los and Angeles and St and Catharines. Can this be corrected? Specifically, Los Angeles be identified as one game and St. Catharines be identified as one game?
    Desired Result to assess if expected game = actual game
    Expected Games Vs. Actual Games
    5. If they played the games they were expected to play, can you insert a formula such as if expected game = actual game conditionally format the expected game to green or add a column at the end with a check mark? I am looking for players who actually played the expected game. This will help assess quickly why they did not play the game they should have played and schedule games better in the future.

    any player could be playing several games different from what they were expected to play so it is not a classic v look up

    https://skydrive.live.com/redir?resid=93D5E215410A4CEC!125&authkey=!ACT01q4PVxOuU_c

    for above link – I have added a comment in cell f11 and i13 to provide illustration of what I am referring to regarding splitting of game names incorrectly (Los Angeles or St. Catharines are considered 2 games)

    Reply

    • Hi,

      You may refer to my solution in this workbook

      Click on the square box 2 at the top left of the workbook to expand columns. The columns so unhidden will show you individual games and scores. Have also applied conditional formatting to compare Actual games played with Expected games played. I have also corrected for the comma problem in Los Angeles.

      Let me know how it works.

      Reply

  • hi Sir Ashish;

    I have a minor request.

    in order to get actual data, I only want to use player name and id as a constant . Since in actual data the remainder of the constants do not apply.

    so it is a smaller formula. Can you adjust?

    Thanks

    Reply

    • Hi,

      The existing formula only uses Player id as the lookup_value (it does not take Player Name into account at all) for generating Actual as well as Expected data. I doubt whether I can make the formula any smaller that what it already is.

      Reply

  • Hi,

    Delete columns C, D and E from all the three sheets. You may (if you wish) delete it from only the Roster sheet as well (leaving column C, D and E) on the other two sheets. In either case, the solution will remain unchanged.

    Reply

  • Completed!

    Thank you sir for your tremendous Excel help..

    I have submitted my assignment with pride and received much thanks!!

    Reply

  • Hi, I want to use vlookup on a column where duplicate values exists. In the Index Column, there are rows with hyphen value and I want vlookup to move to the next row when there is a hyphen and pick the value from the row where actual value exists. is it possible?. here is the example

    In the below sample table, A493-1 repeated in the column A and I want to skip the first occurrence because it has ‘-‘ value. I would like to pick the value in the next row. Thanks for the help…

    ID Dev Manager Tool
    A492-1 Joe Smith CVS
    A492-7 John Smith CVS
    A492-8 Joe Smith JIRA
    A493-1 John Smith –
    A493-1 Joe Smith JIRA
    A493-3 John Smith –
    A493-3 Joe Smith CVS
    A493-5 John Smith –
    A493-5 Joe Smith CVS
    A493-6 John Smith JIRA

    Reply

    • Hi,

      Try this

      1. Suppose your data is in range A1:C11 of worksheet named Actual Data. Headings are in A1:C1
      2. In another worksheet named lookup, your ID’s are listed from cell A3 downwards
      3. In cell B3 of the lookup worksheet, enter the following formula and copy down

      =INDEX(‘Actual Data’!$C$2:$C$11,MAX(INDEX((‘Actual Data’!$A$2:$A$11=Lookup!A3)*(CODE(‘Actual Data’!$C$2:$C$11)<>45)*(ROW(‘Actual Data’!$A$2:$A$11)-ROW(‘Actual Data’!$A$1)),,)),1)

      Hope this helps.

      Reply

  • hi all i have

    A3=pete

    A4=sam

    A5=perkins

    A6=sam

    A7=sam

    A8=perkins

    A9=dave

    A10=perkins

    A11=sam

    A12=louis

    i would like to make a search string in B3:B11 so that if i enter for example sam and perkins i get successively and ordinally all the items related to it like:

    C1=sam

    C2=sam

    C3=sam

    C4=sam

    C5=perkins

    C6=perkins

    C7=perkins

    if there is aformula suggested will it work also for numbers ?? this is my issue …any ideas??

    ashish can u please assist a bit ?… after a long thinking i managed to do the folowing :

    i wrote in cell F3=INDEX($A$3:$A$12,MATCH(SMALL(COUNTIF($A$3:$A$12,”<"&$A$3:$A$12),ROW(A1)),COUNTIF($A$3:$A$12,"<"&$A$3:$A$12),0)) ctrl+shift+enter and i dragged down to cell F12

    then i made my search string from i3:i7 then i wrote in L3=INDEX($F$3:$F$12,SMALL(IF(ISNUMBER(SEARCH(TRANSPOSE(IF($I$3:$I$7″”,$I$3:$I$7)),$F$3:$F$12)),ROW($A$3:$A$12)-MIN(ROW($A$3:$A$12))+1),ROW(A1))) ctrl+shift+enter

    NOW MY BIG issue : im trying to replace INDEX($F$3:$F$12 and TRANSPOSE(IF($I$3:$I$7″”,$I$3:$I$7)),$F$3:$F$12)), with my formula located in F3 in order to avoid using $F$3:$F$12 …so i tried :INDEX($A$3:$A$12,MATCH(SMALL(COUNTIF($A$3:$A$12,”<"&$A$3:$A$12),ROW($A$3:$A$12)),COUNTIF($A$3:$A$12,"<"&$A$3:$A$12),0)) ……. but it wont work …is there a way ????

    Reply

    • Hi,

      Try this

      1. Suppose headings are in A2:B2 and they are in Bold face
      2. Type Sam and Perkins in range B3:B4
      2. Select A2:A12 and press Ctrl+T > OK
      3. Select B2:B4 and press Ctrl+T > OK
      4. In cell C3, enter the following array formula (Ctrl+Shift+Enter)

      =IFERROR(INDEX($A$3:$A$12,SMALL(IF(TRANSPOSE($B$3:$B$4)=$A$3:$A$12,IF(TRANSPOSE($B$3:$B$4)>=$A$3:$A$12,ROW($A$3:$A$12)-ROW(A$2))),ROW(1:1)),1),"")

      5. Copy this formula down.

      Hope this helps.

      Reply

  • Been looking through all of this and have a question.

    I have prodIDs on Row A1:A150 there can duplicate prodIDs per row

    I have corresponding Part numbers in Col B1 – K150 associated to each PRODID

    So how can I use your formula to generate a list of all part numbers in the array Col B1:K150 associated to the Unique ProdIDs list?

    Thanks in advance

    Reply

  • Hi Ashish,

    can you please help me with this?

    using VLOOKUP I get an error #N/A. for the below data

    LOW HIGH INPUT OUTPUT R look UP
    1 4 0 F R1 R2
    6 10 8 T R2
    11 13 0 F R3
    16 20 0 F R4
    25 30 26 T R5

    LOW HIGH INPUT OUTPUT R look UP
    1 4 0 F R1 #N/A
    6 8 0 F R2
    11 13 0 F R3
    16 20 0 F R4
    25 30 0 F R5

    FORUMLAS:

    D2:D6 –> =IF(AND(C2>=A2,C2 =VLOOKUP(“T”,D2:E6,2,FALSE)

    ————-
    i am aware of this error,it occurs when there is no matching (lookup)value.

    I dont want to see this error.Instead want FALSE or F as an o/p when the INPUT Column entered value doesn fall in the range.

    Thank you!

    Reply

  • I got that Ashish!

    I was looking for this formula :

    =IF(ISNA(VLOOKUP(“TRUE”,D2:E53,1,FALSE)),”FALSE”,VLOOKUP(“TRUE”,D2:E53,1,FALSE))

    Thank you!!

    You are doing Amazing job!! 🙂

    Reply

    • Thank you for your kind words. If you are using MS Excel 2007 + versions, you may use this smaller formula

      =IFERROR(VLOOKUP(“TRUE”,$D$2:$E$53,1,FALSE),”FALSE”)

      Reply

  • Problem: Data is provided in a vertical manner. I want to use” look up a value in a list and return multiple corresponding values” in a horizontal format in another tab in my worksheet. Formula (in red) provided in Help Article does not seem to work. Also I’m not sure how to copy it over.What part of the formula needs to be “locked?
    I’m giving the arrays unique names (vs using your method of locked ranges.)
    Using Excel 2003

    original data
    customer model price range/array names used:
    123 A $5 custprice = C9:E15
    123 B $8 custnum = C9:C15
    123 C $12
    124 A $6
    124 D $14
    125 C $13
    125 D $15

    Desired format
    customer 1st model 1st mod price 2nd model 2nd mod price 3rd model 3rd mod price
    123 #VALUE! #VALUE!
    124
    125

    Reply

  • You must be using a newer version of Excel, as I got a file conversion message when I opened your file, and the file had no formulas once it converted to my version. It just showed {=#VALUE!). I’m not sure if the file conversion also clobbered other tabs. One of the issues i’m having is I want the lookup data on a separate tab from teh results.

    I am using Excel 2003.

    Reply

  • Ok, here is the upload and I’ll try to explain what I’m trying to do a little better.

    Background:
    This workbook is supposed to provide a daily job listing for our work crews. They have set jobs for set days. On the Ramos tab (blue), the account manager will just select the day they are working on, 1-5. I used number instead of M, T, W because the work may be shifted due to holidays or special circumstances. Selecting the day, populates the account name and address on the Ramos tab, these cells are normally locked. Then if there is any additional work they are assigned, it is entered on the Add-Ons (green) tab. All of the info on the Ramos tab then feeds to the Today’s Work(orange) tab which is normally hidden. This then feeds the Ramos Work(red) tab, which is printed and given to the work crew. Accounts are assigned crews, days and service order on the Account(yellow) tab.

    Issue:
    In the version uploaded, I have Ramos Day 1 selected. There is normally only 1 job that day. I added an additional 4 job to the Add-Ons tab for total of 5 jobs. But I only get 4. I want it to look for Ramos Day “x” jobs on the Accounts tab and then looked for Add-On jobs starting with the first instance.

    Hope that was a little clearer.

    File Link:
    http://sdrv.ms/13mxSXT

    Reply

  • In column B are names (in each cell one word or a blank) in rows 3 to 18.

    In column AC in the corresponding cells are formulae resulting in positive or negative numbers or blanks or zeroes.

    In cell C26 I want a list of those names in column B for which the corresponding cells in column AC show a number larger than 95000.

    By “list” I mean that in C26 I want something like John — Paul — Sarah — William — Esther

    Can this be obtained with an array formula? If not, what formula will do the trick?

    Thanks in advance for your help.

    Jack Sons.

    Reply

  • I get during downloading a message saying that morefunc contains a virus because of which it has been removed. What now?
    Is MCONCAT really necessary, is it possible to use CONCAT?

    Jack.

    Reply

  • Hi All,

    I want to lookup the value from a particular column having repeated values :-

    for example : from cell A1: B6

    Name Month Commision
    A Jan 12
    B Jan 123
    C Jan 3
    A Jan 211
    B Feb 11

    I tried below formula but unable to show the result having criteria in E2 & F2 :-
    and formula in cell G2
    Name Repeated Salary
    a 1 =SUMPRODUCT(($E$2=$A$2:$A$6)*COUNTIF($A$2:A2,A2)=$F$2,$C$2:$C$6) also I tried the formula =INDEX($A$1:$C$6,MATCH(E2,$A$2:$A$6,0)*COUNTIF($A$2:A2,A2)=$F$2,0).

    Please sugggest what modifications required in the above formula or its all wrong ?

    Thanks in advance.

    Regards,
    Rajender

    Reply

      • Hi Ashish,

        Thanks a lot, its working fine.
        Could you please assist how the below portion of the function works ?
        ROW(INDIRECT(“1:”&ROWS($C$1:$C$6))))=0,10000,
        and how the above calculations works ?

        I am unable to understand the calculation part of the above , how it calculates step by step ?

        or Is there any link where I can find this type of calculation step by step to understand easily ?

        Thanks in advance for your assistance.

        Regards,
        Rajender

        Reply

        • Hi,

          You are welcome. The best way to understand step by step calculations would be by using the F9 key (highlight the portion of the formula which you cannot understand and press the F9 key). The F9 key will show you the result instead of the formula.

          Also, the explanation of a similar problem be studied from here

          Hope this helps.

          Reply

  • Hi,

    Question & Requirement

    When I enter the “Menu Name” in the cell ‘B2’, the Menu Code is generated automatically in the cell ‘F2’ and the menu name appears automatically in the cell ‘B5’ also. (Actually I have planned this Item Menu Master List to be in a separate sheet.)
    I have planned the following
    (1) Menu Name & Menu Code may not be sorted as any item i want to add or remove will be done from the ‘Item Menu Master’.

    The items listed for the ‘Menu Name’ selected should list all the list of items (multiple occurrences) in the space provided above. (For Eg. If I select Item Code as ‘102’, then it should list two items from specific columns of ‘Item Menu Master’ above.

    Item Name Chicken Biriyani Menu Code 101

    Item List for above Menu Code
    Menu Name : Chicken Biriyani
    Measured For : 1 Kg
    Item(s) Qty Unit Rate Amount
    Chicken 0.75 Gms 1 0.75
    Onion 0.15 Gms 25 3.75
    Tomato 0.15 Gms 0.89 0.13
    Basmati Rice 1 Kg(s) 96 96.00

    Item Menu Master
    Menu Code Menu Name Item(s) Measured For Qty Unit Rate
    101 Chicken Biriyani Chicken 1 Kg 0.750 Gms 1.00
    101 Chicken Biriyani Onion 1 Kg 0.150 Gms 25.00
    101 Chicken Biriyani Tomato 1 Kg 0.150 Gms 0.89
    101 Chicken Biriyani Basmati Rice 1 Kg 1.000 Kg(s) 96.00
    102 Alloo Parotta Potato 1 Kg 1.000 Kg(s) 28.00
    102 Alloo Parotta Atta 1 Kg 1.000 Kg(s) 39.00

    Could you please help me out in this listing.

    I am attaching the file. Please chek the reply mail in which I have send the xl file.

    Thanks

    Richard

    Reply

  • Lookup and Return Names With Non- Consecutive Rows

    Having an issue with an array formula the community helped me with previously. Have been given a new wrinkle in the data. I was using an array formula because all the data was being captured. {IFERROR(INDEX(‘sheet2’!$A$2:$A$14,SMALL(IF(‘sheet2’!$C$2:$C$14=”X”,ROW(‘sheet2’!$A$2:$A$14)-ROW(‘sheet2’!$A$1)),C$2),1),””)}. But now I have to skip certain rows. I’ve tried using my current array formula but can’t get it to work. {IFERROR(INDEX(‘sheet2’!$A$2:$A$5,$A$9:$A$14:SMALL(IF(‘sheet2’!$C$2:$C$5,$C$9:$C$14=”X”,ROW(‘sheet2’!$A$2:$A$5,$A$9:$A$14)-ROW(‘sheet2’!$A$1)),C$2),1),””)}. Can this still be done with an array formula? I need to skip rows 6, 7, 8.

    SHEET 1
    1 Job Titles Dept. 1 2 3 4
    2 Claims CSI L. Smith
    3 Outb Calls CSI C. Pepper
    4 Notifications CSI A. Hawkins J. Ramsey
    5 Evaluator CSI

    SHEET 2
    1 Staff Name Mgr Claims Outb Calls Notifications Evaluator
    2 A. Hawkins Adams x x
    3 C. Pepper Adams x
    4 C. Sanders Adams
    5 D. Sanders Adams
    6 J. Arcomone Lewis
    7 J. Rast Lewis x
    8 J. Montalvo Lewis x
    9 J.Ramsey Jones x
    10 K. Selman Jones
    11 L. Smith Jones x
    12 L.Young Jones
    13 M. McBride Jones
    14 R. Wilson Jones

    Reply

  • I have a table as shown in the picture below where various ID are ranked 1-5 over a range of 9 skills

    Image

    Desire output where each skill shows those with same ranking in groups

    column 1 has header of ID any number of unique ID, say ID1 – ID10
    Other column each a skill name, see http://answers.microsoft.com/en-us/office/forum/office_2013_release-excel/not-sure-best-way-to-get-the-results-i-need-from-a/7d9feb0d-6bcc-4b5f-b486-9d76c8e687fb for pictures

    Should a pivot table be able to do this, so far none of my attempts are even coming close.

    Reply

    • Hi,

      You may refer to my solution in this workbook.

      Please note that to use this solution, you will have to download and install the Morefunc add-in from here.

      If you are using Excel 2010 and higher versions, then please read the point 4 of the link very carefully.

      Hope this helps.

      Reply

  • Hi,

    I’m using this formula here from F5-F24:

    =IF(ISERROR(INDEX(Sheet2!$A$2:$B$30,SMALL(IF(Sheet2!$A$2:$A$30=$C$2,ROW(Sheet2!$A$2:$A$30)),ROW(1:1)),2)),””,INDEX(Sheet2!$A$2:$B$30,SMALL(IF(Sheet2!$A$2:$A$30=$C$2,ROW(Sheet2!$A$2:$A$30)),ROW(1:1)),2))

    and it’s working well except for a small detail. The returning results are complete but include duplicates. Is there a way to include only unique results?

    Thank you very much.

    Reply

    • Hi,

      I cannot understand your question. Share sample data, the business question and the expected solution. Upload your file to SkyDrive and share the link of the uploaded file here.

      Reply

  • Hi all….
    Below is the data, in Col A, 37 value is repeating with different value in col B.

    Row # Col A Col b
    1 25 a
    2 37 b
    3 15 c
    4 37 d
    5 21 e
    6 37 g
    7 56 h
    8
    9
    10
    11 25 a
    12 37 b
    13 15 c
    14 37 b
    15 21 e
    16 37 b
    17 56 h

    from cell A11 i have enter same value and as above mention but i didn’t get same value of col B in cell B11, I used Vlookup but i get first found value (B against 37 data).
    Also index match function given the same value.

    I want same data as above

    Reply

    • Hi,

      In cell B11, enter this array formula (Ctrl+Shift+Enter) and copy down

      =IF(ISERROR(INDEX($A$2:$B$8,SMALL(IF($A$2:$A$8=$A11,ROW($A$2:$A$8)-ROW($B$1)),COUNTIF(A$11:A11,A11)),2)),””,INDEX($A$2:$B$8,SMALL(IF($A$2:$A$8=$A11,ROW($A$2:$A$8)-ROW($B$1)),COUNTIF(A$11:A11,A11)),2))

      Hope this helps.

      Reply

  • Hi Ashish

    Thanks for your tutorial on how to return multiple corresponding values based on a cell reference.

    http://office.microsoft.com/en-us/excel-help/how-to-look-up-a-value-in-a-list-and-return-multiple-corresponding-values-HA001226038.aspx

    I can get your formula to work when I keep it on A1:A7 but when I move the array table to another section of the spreadsheet eg., H11:H20 I just cannot make it work.
    I’ve tried changing the column number, the row selections – no luck.

    If you are able to help at all I would really appreciate it.

    James

    Here’s the spread I’m working on in case you want to take a look.
    https://www.dropbox.com/s/b1u5z1cmcf0hhqi/Checklist.xlsx

    Reply

  • Sir
    I have a table like given below
    I want to find a particular ID for know how much given him and i want know the date also

    can you help me

    Date Id Name Amount
    01-01-14 A10 Abilash 100
    25-01-14 B10 Badan 150
    28-01-14 A10 Abilash 150
    02-02-14 C10 Chandu 120
    15-02-14 B10 Badan 180
    23-02-14 A10 Abilash 200
    01-03-14 C10 Chandu 150

    Reply

    • Hi,

      Do you want to know the total amount which was given to each person? If yes, then if the individual amounts were given on different dates, then which data would you like to show. Please share your expected result.

      Reply

  • Hi Ashish,

    Thank you for the very informative video. I had no idea Power Query existed beforehand. Now I’m attempting to apply it to my work.

    Is it possible to have two Power Query tables side by side in one spreadsheet? I found that Excel won’t let me refresh both tables, just one.

    Victor

    Reply

    • Hi,

      You are welcome. I am glad you found the video informative. You can place two Power Query tables side by side. Go to Data > Refresh > Refresh All to refresh all connections in the workbook.

      Reply

  • Table 1 Table 2
    Amount Amount 53 60 115 101 141 100 68 107 181 98 170 188 79 120 117 96 155 51 123 185
    185
    123
    51 9. Using formula, reverse amount in Table 1 as shown above in Table 3
    155
    96 Table 3
    117 Amount
    120
    79
    188
    170
    98
    181
    107
    68
    100
    141
    101
    115
    60
    53

    Reply

    • Hi,

      Neither have you bothered to describe your question, nor does your data appear correctly. Furthermore, courtesy demands that you atleast start a conversation with a Hi.

      Reply

  • Helo Ashish,

    I hope that I can explain myself well enough without providing an example cause I don’t want to waste your time but it’s the only way I can do that for now, so here I go.

    What I am needing to do is have this wonderful code search two seperate areas for the “key” paramater which is a date and return all the items found in area one and then start over in area two once it is completed with area one.

    Here is how I “tried” to modify it…

    =IF(ISERROR(INDEX($D$2:$F$202,SMALL(IF($D$2:$F$202=N$2,ROW($D$2:$F$202)-ROW($F$1)),ROW($A1:$C1)),1))=FALSE,CONCATENATE(“A”,INDEX($D$2:$F$202,SMALL(IF($D$2:$F$202=N$2,ROW($D$2:$F$202)-ROW($F$1)),ROW($A1:$C1)),1),” @ “,INDEX($D$2:$F$202,SMALL(IF($D$2:$F$202=N$2,ROW($D$2:$F$202)-ROW($F$1)),ROW($A1:$C1)),3)),IF(ISERROR(INDEX($G$2:$I$201,SMALL(IF($G$2:$I$201=N$2,ROW($G$2:$I$201)-ROW($I$1)),ROW($A1:$C1)),1))=FALSE,CONCATENATE(“B”,INDEX($G$2:$I$201,SMALL(IF($G$2:$I$201=N$2,ROW($G$2:$I$201)-ROW($I$1)),ROW($A1:$C1)),1),” @ “,INDEX($G$2:$I$201,SMALL(IF($G$2:$I$201=N$2,ROW($G$2:$I$201)-ROW($I$1)),ROW($A1:$C1)),3)),””))

    N$2 Is any given date
    The “A” & “B” is my Team Idents (They each perform specific tasks so on a given day one or both teams may have work as well as visit the same customer)
    Col “D” & “G” has Unique Customer Codes
    Col “E” & “H” has the date to search for
    Col “F” & “I” has the times of that date that the teams will perform work

    So My Output I am looking for is…

    Jul. 22, 2014
    A232 @ 11:50PM
    A234 @ 8:15PM
    B232 @ 10:00AM
    B375 @ 2:00PM

    My Problem with the above modified code is that “IF” there is a date that Team “A” & “B” has work to do it’ll only show Team “A” but if team “A” does not have work on that day then it will certainly show Team “B”s work but I cannot get it to show both teams work on the same date.

    Eventually I would like to have three tabs for this. Tab 1 for the dates with outlines; Tab 2 for Team “A”s workload; Tab 3 for Team “B”s workload.

    Thank You So Much for Your Time and Any Help Possible!

    ~Rob

    Reply

    • Hi,

      I will be able to help you if you share the file, explain the business problem and show me the result that you are expecting. Please share a link from where I can download the file.

      Reply

      • Hi Ashish,

        I am amazed by your great knowledge, I have a very similar problem but I only need the total of all the same item in 1 cell, in your example it would be for Ashish 1200 ( i don’t want to see the breakdown)
        This is my formula
        =IFERROR(INDEX(‘all PO”s’!$C$2:$C$39,MATCH($B9&$A9&D$1,’all PO”s’!$I$2:$I$39,FALSE),1),””)
        the problem is it only returns the first match and I have multiple matches and I need the total.
        I would love to send you the whole file, where can I send it?
        Thanks a million in advance
        Reuven

        Reply

        • Hi,

          Thank you for your kind words. From your description, it looks like you may to use the SUMIF() or SUMPRODUCT() function. If you need more specific help, then share the link from where I can download the file. Please clearly mark the cell(s) where you want the result and also show the expected result.

          Reply

          • Thanks again and as per your request I shared the file in the following link,
            https://onedrive.live.com/redir?resid=47B9BBE3EB3B5824!107&authkey=!ADoGGWK_zCGySkg&ithint=file%2cxlsx
            As you can see in the “Main sheet” I have the same formula multiple times and it takes the info from “All PO’s” the problem is at ‘main sheet’ cell D9 (highlighted in red) it needs to take info from ‘all PO’s’ cell C23+C24 (because they both have the same item code in collumn I) and it only gives me C23 so if you can please tell me how to fix it i would really appreciate it.

          • Hi,

            Try either of the following in cell D9 of main sheet and then copy down/right

            1. =SUMPRODUCT((‘all PO”s’!$G$2:$G$39=’main sheet’!$A9)*(‘all PO”s’!$A$2:$A$39=’main sheet’!$B9)*(‘all PO”s’!$H$2:$H$39=’main sheet’!D$1)*(‘all PO”s’!$C$2:$C$39)); or

            2. =SUMIFS(‘all PO”s’!$C$2:$C$39,’all PO”s’!$G$2:$G$39,’main sheet’!$A9,’all PO”s’!$A$2:$A$39,’main sheet’!$B9,’all PO”s’!$H$2:$H$39,’main sheet’!D$1)

            Hope this helps.

      • Thanks again, I used it and it works greatly, however you created the range to only work til row #39 i need it to be for the whole columns of each, so I tried to change the formula slightly to
        =SUMPRODUCT((‘all PO”s’!G:G=’main sheet’!$A9)*’all PO”s’!A:A=’main sheet’!$B9)*(‘all PO”s’!H:H=’main sheet’!D$1)*(‘all PO”s’!C:C))
        or i tried =SUMIFS(‘all PO”s’!C:C,’all PO”s’!G:G,’main sheet’!$A9,’all PO”s’!A:A,’all PO”s’!H:H,’main sheet’!D$1)
        and in both cases it returns an error.
        so I had no choice so i changed it to
        =SUMPRODUCT((‘all PO”s’!$G$2:$G$5000=’main sheet’!$A9)*(‘all PO”s’!$A$2:$A$5000=’main sheet’!$B9)*(‘all PO”s’!$H$2:$H$5000=’main sheet’!D$1)*(‘all PO”s’!$C$2:$C$5000))
        which is fine but not ideal, is there another way to do it?

        Reply

        • Hi,

          Ensure that the range of the formula is till row 39 only. This range can be made to auto expand if convert the range into a Table. Select A1:I39 of the all PO’s sheet and press Ctrl+T > OK. Now as data will be added after row 39, the range of your formula will keep expanding automatically.

          Hope this helps.

          Reply

  • SIR, BUT I’M NOT GETTING ALL THE INVOICE NO’S, MEANS IT SHOULD EXCLUDE THE DUPLICATE VALUES AND RETURN UNIQUE VALUES.

    KINDLY HELP.

    Reply

  • This is such a great resource! Ashish, you are awesome!

    I am trying to return to All Shifts tab the rows that would contain a percentage equal to or greater than 95%. See the below. I have tried multiple variations of the formula’s here and can’t seem to get it. Any help would be appreciated!

    All Shifts Name First Round Second Round Disparity
    Shift 1 Joe 26 25 96.15%
    Shift 1 Ken 41 38 92.68%
    Shift 2 Mark 56 55 98.21%
    Shift 3 John 27 26 96.30%

    Shift 1
    Name First Round Second Round Disparity
    Joe 26 25 96.15%
    Mary 55 38 69.09%
    Ken 41 38 92.68%

    Shift 2
    Name First Round Second Round Disparity
    Mark 56 55 98.21%
    Larry 25 21 84.00%
    Sue 26 20 76.92%

    Shift 3
    Name First Round Second Round Disparity
    Tom 40 25 62.50%
    Mellissa 11 8 72.73%
    John 27 26 96.30%

    Reply

  • Ok. I appreciate the feedback. I need the sheets separate for nurse rounds.

    How about if I used three lookups as in the below – one for each sheet. Is this possible to pull this data onto one separate sheet?

    Thank you again!

    Shift 1 Name First Round Second Round Disparity
    Shift 1 Joe 26 25 96.15%
    Shift 1 Ken 41 38 92.68%

    Shift 2 Name First Round Second Round Disparity
    Shift 2 Mark 56 55 98.21%

    Shift 3 Name First Round Second Round Disparity
    Shift 3 John 27 26 96.30%

    Reply

    • Hi,

      No that will still not be possible. For my formula to work, all data should be on one worksheet. So, we will let your three worksheets remain intact and dynamically append data from multiple worksheets into a single one (via the method described in the link shared in my previous post).

      Reply

  • So what you are saying is I can’t build a formula that will look into the other sheets (tabs) and return the rows where the percentage is greater than 95%?

    I really don’t want a pivot table. I need to be able to have the shifts update from the rounds and, hopefully, have it displayed in the Cover sheet.

    Thanks again.

    Reply

  • Thanks for the help. I will mess with it to do what I need. There might be a VBA function that I can program that might do what I need.

    Keep up the good work!

    Reply

  • Hello Ashish,
    I tried using your formula below, but the result does not show in the cell. Please help.

    =IF(ISERROR(INDEX($B$3:$C$9,SMALL(IF($B$3:$B$9=$B$12,ROW($B$3:$B$9)-ROW($C$2)),ROW(A1:C1)),2)),””,INDEX($B$3:$C$9,SMALL(IF($B$3:$B$9=$B$12,ROW($B$3:$B$9)-ROW($C$2)),ROW(A1:C1)),2))

    Reply

    • Hi,

      I cannot point out a mistake with your formula. Just ensure the following:

      1. That you confirm the formula with Ctrl+Shift+Enter (after pressing F2); and
      2. Spelling of the entry in cell F2 should match the spelling of entries in range B3:B9 (there should be no extra spaces)

      If it still does not help, then upload the file to OneDrive and share the link of the file here.

      Reply

  • Hi Ashish,

    Please see the following and let me know if you can help!

    On the next page I have Bill of material numbers 35 and 987 in column A.

    Search Here Data populates in these cells

    So for example if I type 35 in the search area it should draw the data and look like this.

    35 71920 Nuts
    98789 Bolts
    58245 Ubolts
    123 Screws
    786 Washers
    987485 L Bracket
    2546 U Bracket
    35896 S Bracket
    5468 Hinge
    98765412 Iron Rod
    987789 Copper Rod

    If I type 987 in the search area it should look like this.

    987 89654 Pine Dowel
    7541 Cedar Dowel
    685 Maple Dowel
    25454 Oak Dowel
    25456 Iron Dowel

    The data would be stored on another page in the same workbook, but I need it to draw to certain cells on a seperate by only typing in the Bill of Materials Number.

    Reply

    • Hi,

      Your question is not clear. Upload your workbook to OneDrive and share the link of the workbook here. Please also give a detailed description of the expected result.

      Reply

  • Hi Ashish,
    I have been trying to make one template, where I will have 2 sheets under same workbook.

    In sheet 1, I will enter the field value based on which Excel should search for relevant strings or rows in sheet 2 and then in sheet one it will populate all the values.

    I am using the following formula but for some reason its not working.

    =IF(ISERROR(INDEX(‘BPO Structure Dictionary’!$H$3:$AE$293,SMALL(IF(‘BPO Structure Dictionary’!$H$3:$AE$292=$F$4,ROW(‘BPO Structure Dictionary’!$H$3:$AE$291)),ROW(‘BPO Structure Dictionary’!1:1)),2)),””,INDEX(‘BPO Structure Dictionary’!$H$3:$AE$292,SMALL(IF(‘BPO Structure Dictionary’!$H$3:$AE$291=$F$4,ROW(‘BPO Structure Dictionary’!$H$3:$AE$292)),ROW(‘BPO Structure Dictionary’!1:1)),2))

    Name of sheet 2 is BPO Structure Dictionary

    Reply

    • Hi,

      Try this array formula (Ctrl+Shift+Enter)

      =IF(ISERROR(INDEX(‘BPO Structure Dictionary’!$H$3:$AE$293,SMALL(IF(‘BPO Structure Dictionary’!$H$3:$H$292=$F$4,ROW(‘BPO Structure Dictionary’!$H$3:$AE$291)-ROW(‘BPO Structure Dictionary’!$H$2)),ROW(‘BPO Structure Dictionary’!1:1)),2)),””,INDEX(‘BPO Structure Dictionary’!$H$3:$AE$293,SMALL(IF(‘BPO Structure Dictionary’!$H$3:$H$292=$F$4,ROW(‘BPO Structure Dictionary’!$H$3:$AE$291)-ROW(‘BPO Structure Dictionary’!$H$2)),ROW(‘BPO Structure Dictionary’!1:1)),2))

      Reply

  • Data range is given below. I am looking for formula which may help me
    in looking up name against same value.

    Data Range:

    B2:B4 C2:C4
    John 1
    George 2
    Stephen 2

    Where Required:

    B7:B9 C7:C9
    1 ?
    2 ?
    2 ?

    Reply

    • Hi,

      In cell C7, enter this array formula (Ctrl+Shift+Enter) and copy down

      =IFERROR(INDEX($B$2:$B$4,SMALL(IF($C$2:$C$4=B7,ROW($B$2:$B$4)-ROW($B$1)),COUNTIF(B$7:B7,B7)),1),””)

      Hope this helps.

      Reply

  • Hi Ashish,

    I am using a formula similar to the function that you are referencing about however I am having a small issue with it.

    Please see example below;

    Acc No: Sub Acc
    V958874 226243
    V958874 246956
    V958874 264197
    V103234 241560
    V103234 264020

    I would like all values that reference V958874 (cell J4 in the formula) in the sub account returned.

    I am getting the Sub Acc values returned however it should stop at 264197. The formula is not picking up the first value that corresponds to V958874 in sheet 4. It moves to the next value sort of like A1 gives back B2 cells corresponding result instead of giving B2 result.

    Instead I am getting the first value 241560 returned even though the account number it references has changed.

    The formula I am using is listed below;

    =IF(ISERROR(INDEX(Sheet4!$A$2:$B$11805,SMALL(IF(Sheet4!$A$2:$B$11805=$J$4,ROW(Sheet4!$A$2:$B$11805)),ROW(1:1)),2)),””,INDEX(Sheet4!$A$2:$B$11805,SMALL(IF(Sheet4!$A$2:$B$11805=$J$4,ROW(Sheet4!$A$2:$B$11805)),ROW(1:1)),2))

    Is there a better formula or way of doing this?

    Can you see an error in the formula that I am using to be giving me this result?

    Thanks if you are able to help.

    Reply

    • Hi,

      Try this array formula (Ctrl+Shift+Enter)

      =IFERROR(INDEX(Sheet4!$A$2:$B$11805,SMALL(IF(Sheet4!$A$2:$A$11805=$J$4,ROW(Sheet4!$A$2:$B$11805)-ROW(Sheet4!$A$1)),ROW(1:1)),1),””)

      Hope this helps.

      Reply

      • Hi Ashish,

        Thanks for coming back to me.

        I tried the formula and it didn’t work for me.

        The value that your formula returned was the value that I am referencing in cell $J$4 on sheet1. The value $J$4 is the Acc No:V958874. this value also appears in Sheet4 Column A. (I am using sheet1 to have the results from sheet4 returned).

        I need the value that is in sheet4 column B that corresponds to V958874 returned, as per my original example.

        The formula that I pasted does work, however it misses the first value in column B that the value first references for some reason. it does return the first value in the next reference after the formula has pasted all relevant matches to the V958874.
        In my example mentioned the value 241560 from sheet4 column B is returned at the end. This should not happen as the reference for this number in column A is V103234

        I hope this is clear.

        Are you able to help?

        Thanks,
        Rod

        Reply

  • Hi Ashish,

    You are a legend!

    This time it worked perfectly.

    I need to ask you another question on what I call a reverse v look up if you are able to help.

    Sheet 1 is a template, I am looking up information from sheet 4 as you could see.

    I would like to be able to enter information into sheet 1 and have it backed up in sheet 4.

    I have been able to do this however it does not appear to be saving the way o would like.

    I am entering a figure in sheet 1 Column C15. I have done a v lookup in sheet 4 column C. The figure appears next to the acc no and sub account number in sheet 4 column C.

    I save the changes, then I enter another sub account number in sheet 1 column J5.

    The figure in cell C15 now appears next to the new sub account number in sheet 4 column C, however the previous figure that was aligned next to the previous sub account number comes up as #N/A.

    Is there a way that I can do this so that the figure from cell C15 stays in column C adjacent to the previous sub account number that I entered and save regardless of the new sub acc number being entered?

    I eventually want to have a figure next to most sub acc numbers in sheet 4 column C.

    I have created a link below for you to have a look if it is possible to help.

    https://onedrive.live.com/redir?resid=662E642D16E6FD84!119&authkey=!AEcqnL3hzL-YU7w&ithint=file%2cxlsx

    Thanks again.

    Rod

    Reply

    • Hi,

      No, that cannot happen. When Account numbers in column A change because of a change in cell J5 of sheet1, the value you are looking up in sheet4, undergoes a change and therefore the previous result is not retained.

      Reply

  • Hello Ashish,

    The Power Query table that obtains multiple values from a single query is what I need across multiple worksheets.

    I have monthly worksheets with an “expenses” table in each. There are over 120 worksheets. As an example, I would like to search all of them for 2014 to summarize gasoline expenses.

    Unfortunately, Power Query does not seem to allow inputting an array of worksheets.

    I currently have an array formula that only returns the first found and exact string only: {=VLOOKUP(B2,INDIRECT(“‘”&INDEX(SheetList,MATCH(1,–(COUNTIF(INDIRECT(“‘”&SheetList&”‘!$C$20:$E$55″),B2)>0),0))&”‘!$C$20:$E$55”),3,FALSE)}. Sheetlist is the worksheet array name on the query sheet, B2 is the query string, and c20:e55 is the table range to search across all worksheets I list in the array.

    This formula does not do a partial string nor does it return multiple values in a table for all instances found, just the first value found after an exact string match.

    What I would like is to submit a partial string query across worksheets ‘SheetA !’ thru ‘Sheet Z!’ and have the results appear in a table. This is just like your single worksheet example with Power Query, but across multiple worksheets. For example, the partial string “gas” would search the same table range in all worksheets and return the amounts entered in the dollar value column (column 3). Also, I would like it to allow a partial string search as the word gas appears with other descriptors such as the station name.

    I’ve read this functionality does not exist in Power Query and requires VBA. Is there a solution?

    Thanks for your time,

    Brett

    Reply

  • Hi

    I have been looking at your solution to search strings when they appear multiple time. I have however come across a couple of challenges.
    I cannot find Power Query in my version of excel and I have tried to use the formula it is not working for me. I must be making a mistake somewhere.
    Is Power Query not available to non-professional versions of excel?
    I am happy to share my spreadsheet if you have time to assist.

    Regards

    Reply

    • Hi,

      Power Query can be downloaded free of cost from the Microsoft website and can be installed on any version (I think). Upload your workbook to OneDrive and share the link here.

      Reply

  • Hello,

    Thank you for taking the time to answer our questions. Maybe you can help me too.
    Your example is for finding Ashish from the column.
    I have a column of numbers that I need to look it up in another column and return the values next to it. “=VLOOKUP(A2,$C$2:$D$300,2,false)” is not working. I get the same value for duplicates.
    I have column A2:A500.
    Then columns C2:C300 and D2:D300. In both columns A2:A500 and C2:C300 there are duplicates and vlookup will return only the first value.
    Can you find solution?
    Thank you,
    Alin

    Reply

  • Hi Ashish,

    I would need your help for below query:

    In Excel, you have a datasheet with 5,000 rows and 40 columns. You have been asked to provide a single worksheet in a new workbook with only those records that have the value “TRUE” in Column Q. Explain how you would create the new workbook.

    Reply

  • Just saying that this was really helpful and after a little bit of tinkering it’s doing exactly what I need without fail.

    Understanding the sytax takes a few minutes but it’s really quite simple and cleverly put together. Kudos.

    Reply

  • I am trying to create a lookup to match mulitple values for duplicates from two workbooks that have multiple columns but am having trouble. I tried the array, but can’t seem to get the correct values or the value only populates in the first couple rows them I only get blanks for the remainders.

    Reply

      • HI Ashish

        I am new to this online blog response thing. This is just a example of the data I am trying to join. I couldn’t send the real data because it has confidential information. Please let me know if you can help.

        My OneDrive link is below:
        http://1drv.ms/1HLFxmi

        Forgot to leave my formula, Thanks

        =IFERROR(INDEX(‘[dup test file 11.12.15.xlsx]Test’!$E$2:$E$16,SMALL(IF(‘[dup test file 11.12.15.xlsx]Test’!$D$2:$D$16=$C2,ROW(‘[dup test file 11.12.15.xlsx]Test’!$E$2:$E$16)),ROWS($G1:G1))),””)

        Thanks
        Denise

        Reply

  • Here is the link

    http://1drv.ms/1HLFxmi

    I am trying to bring the values from column E in the dup test file to column F or G in the Data test file. The value I was trying to use as a lookup was column C in the data test file with column D in the dup test file .

    Reply

  • Hi,

    Its a great solution if the data is in the column format. Do u have any solution if the data is the row format. Please help me with solution

    Reply

  • Hi Ashish,

    I have a big data of one month transactions which includes date invoice no and amount. Another sheet in which i have transactions converted to installments which has only date and amount for that particular month. So i want to v look up and get the invoice no in the second sheet as there is so many duplicate values in both sheets. Please reply ASAP.

    Reply

  • hi,
    I have daily sales report for items on sheet1 and inventory on sheet 2. The sales happen daily and are entered in descending rows. There r 3 columns in sheet 1 – sale, return and counter stock delivery. On sheet 2, is fixed list inventory. I am trying to add formula in sheet 2, where it takes item number and goes to sheet 1, search it in the item number column there and adds values in the corresponding rows. The trick is same item can appear number of times. so add all of them but show number in the same cell in sheet 2. Means it will keep changing as sales or return or stock delivery happens. Is it possible? Please help.

    Reply

    • Hi,

      One can explore the SUMIF() function to solve the problem. But the problem is that you want the value in the same cell to keep changing. That would require a VBA solution.

      Reply

  • Hi Ashish,

    I had a question and would be so grateful if you could spare a couple of minutes.

    So I have a testing sheet ‘4-6-2016’ where I am using vlookup (along with iferror and concatenate functions) to pull corresponding information from sheet, ‘data15’

    I concatenated employee id number with each training name to name a unique column.

    The problem is, the id and training name make a good unique identifier but if someone has done the training multiple times, it chooses the first value it finds (I think).

    Is there a way to get the smaller value i.e: the older date. I saw some posts in your comment section and min or small section might work but I don’t know how to incorporate it within my formula.

    my formula is: =IF($K12<'4-6-2016'!$R$4,"n/a",IFERROR(VLOOKUP(CONCATENATE($A12,L$9),Data16!$O:$U,4,FALSE),"Not found- Please Review"))

    Is there any way to add the small/min condition within this formula to obtain the smaller value from column 4?

    Thank you in advance!

    Reply

      • Ashish I think I may have solved my own problem.

        Basically my vlookup was pulling information from a table where I had multiple matches (apart from the column being looked up). I wanted to pick the smaller value everytime this occurred, so I sorted in ascending order, so smallest dates would get selected everytime.

        Thank you for your willingness to help out!

        Reply

  • Hello Ashish I am trying to calculate the number of times a student was late using the condition

    “for every 4 times late in a term (i.e. 3 months) the student loses a point from final grade”

    I need a code that says: For every time count of L (for specified range) = 4 then score = 1 + each additional instance L=4.

    This is an example of what the first term looks like, where P= Present/not late and L= Present/late

    Last Name Morris
    First Name Tricia
    2/1/2016 L
    2/4/2016 L
    2/8/2016 L
    2/11/2016 P
    2/15/2016 P
    2/18/2016 P
    2/22/2016 P
    2/25/2016 L
    2/29/2016 L
    3/3/2016 L
    3/7/2016 P
    3/10/2016 P
    3/14/2016 P
    3/17/2016 L
    3/19/2016 L
    3/21/2016 P
    3/24/2016 L
    3/28/2016 L
    3/31/2016 P
    4/4/2016 P
    4/7/2016 P
    4/11/2016 P
    4/14/2016 P
    4/16/2016 P
    4/18/2016 P
    4/21/2016 P
    4/25/2016 P
    4/28/2016 P
    4/30/2016 L

    Reply

    • Hi,

      The data has not been pasted properly. Please paste the data in a workbook, upload it to some free file hosting service such as Google Drive, OneDrive etc. and then share the download link here.

      Please also show the expected result on another tab in that workbook.

      Reply

  • could you help please?
    I’ve pivot table report which has duplicate project names. That is the unique name I need to use to make a summary report out of it.

    now how do I create a report from it using that multiple duplicate project names and bring the relevant records to my summary report. please advice.

    Reply

  • Hi sir,
    Start Time in Sheet DG should be in between MF Start and End.
    please help me with Lookup function with multiple criteria else other option.

    Indus_Site_ID DG Start Time
    IN-1095006 9/5/2016 0:02
    IN-1137882 9/5/2016 0:08
    IN-1077456 9/5/2016 3:15
    IN-1095006 9/5/2016 9:14
    IN-1095006 9/5/2016 10:03
    IN-1095006 9/5/2016 12:01
    IN-1248315 9/5/2016 17:05
    IN-1094855 9/5/2016 17:53
    IN-1095006 9/5/2016 19:54
    IN-1077456 9/5/2016 20:00
    IN-1281872 9/5/2016 20:35
    IN-1095006 9/5/2016 23:58

    Indus_Site_ID Start Time End Time Expected Result
    IN-1095006 9/5/2016 0:02 9/5/2016 5:59 9/5/2016 0:02
    IN-1137882 9/5/2016 0:08 9/5/2016 5:01 9/5/2016 0:08
    IN-1281872 9/5/2016 0:13 9/5/2016 6:10 NA
    IN-1248315 9/5/2016 0:15 9/5/2016 3:30 NA
    IN-1077456 9/5/2016 0:16 9/5/2016 6:09 9/5/2016 3:15
    IN-1094855 9/5/2016 0:17 9/5/2016 18:53 9/5/2016 17:53

    Reply

    • Hi,

      Enter this formula in cell D2 of sheet2 and copy down

      =IFERROR(INDEX(DG!$B$2:$B$13,MATCH(1,INDEX((DG!$A$2:$A$13=Sheet2!A2)*(DG!$B$2:$B$13>=Sheet2!B2)*(DG!$B$2:$B$13<=Sheet2!C2),,),0),1),"") Hope this helps.

      Reply

  • Hi Ashish,
    I am trying multiple criteria lookup using Index, Match function form another worksheet. my index Match array function is working but my file is very slow. can you please advise any alternative function to lookup or match multiple criteria from another worksheet.

    cheers
    varun

    Reply

        • Hi,

          Given the size of the data you are dealing with, the best tool to use here is Power Query (a free Business Intelligence add-in from Microsoft for Excel 2010 and 2013). This feature is art of the Native Excel application in Excel 2016 (available under Data > Get & Transform). I just tried it and got the correct result. So you have Power Query/Excel 2016 installed on your system?

          Reply

          • Hi,

            Here are the steps

            1. Created a Query with the name of Data (range of A1:I6 of one table). Just load this as a connection (not as a Table); and
            2. Created a Query with the name of Data1 (range of A1:H6 of the other table). Just load this as a connection (not as a Table); and
            3. The third table basically merges the tables after joining the tables on two columns (Ref doc number and amount). Here is the M query for it

            let
            Source = Table.NestedJoin(data1,{“Ref Document Number”, “Value in Obj. Crcy”},data,{“Document Number”, “Amount in local currency”},”NewColumn”,JoinKind.LeftOuter),
            #”Expanded NewColumn” = Table.ExpandTableColumn(Source, “NewColumn”, {“Assignment”}, {“NewColumn.Assignment”}),
            #”Renamed Columns” = Table.RenameColumns(#”Expanded NewColumn”,{{“NewColumn.Assignment”, “Assignment”}})
            in
            #”Renamed Columns”

            Hope this helps.

          • hi Ashish,

            following steps creating many lines than original data. i cannot figure out why it it creation 10,000 lines when data in only for 800 lines.

            . Created a Query with the name of Data (range of A1:I6 of one table). Just load this as a connection (not as a Table); and
            2. Created a Query with the name of Data1 (range of A1:H6 of the other table). Just load this as a connection (not as a Table); and
            3. The third table basically merges the tables after joining the tables on two columns (Ref doc number and amount). Here is the M query for it

            let
            Source = Table.NestedJoin(data1,{“Ref Document Number”, “Value in Obj. Crcy”},data,{“Document Number”, “Amount in local currency”},”NewColumn”,JoinKind.LeftOuter),
            #”Expanded NewColumn” = Table.ExpandTableColumn(Source, “NewColumn”, {“Assignment”}, {“NewColumn.Assignment”}),
            #”Renamed Columns” = Table.RenameColumns(#”Expanded NewColumn”,{{“NewColumn.Assignment”, “Assignment”}})
            in
            #”Renamed Columns”

            Thanks
            VArun

          • Hi,

            That is happening because for many rows on the GL data worksheet, a combination of Ref Doc Number and Amount is available more than once on the Assignment Data worksheet. You will understand what I mean when you write the following formula in cell G2 of the GL data worksheet and copy down

            =COUNTIFS(‘Assignment Data’!$G$2:$G$1738,’GL DATA’!C2,’Assignment Data’!$E$2:$E$1738,’GL DATA’!F2)

  • Hi there,
    I’m trying to work on a Workbook and falling short of a solution.
    I was wondering if you maybe able to give me some guidance.
    What I have is 2 worksheets Sheet1(Data) and Positions.(Results)

    (WS)Sheet1:
    E1:DD1 Has names of training courses
    A3:A111 Has job position titles
    E3:DD111 is populated with YES if the job position title requires a particular training course.

    (WS)Position:
    I have a dropdown list of sheet1 A3:A111
    Depending on what Job selected from drop down list I would like to search along the row of sheet1 of that job position title for a “YES” if found copy training name course and populate down from Position A12 A13 A14 etc
    I need this to be dynamically done as the YES’s in sheet1 could be changed.
    I can manually enter if’s but this just makes it static for one job position selected.
    I hope I have explained this so that you can understand what I am after?
    Regards

    Reply

      • Hi,

        Try this

        1. The validation in cell D1 of the Position worksheet should be built from range A3:A111 of sheet1
        2. After selecting an entry in cell D1 of the Position sheet, enter this array formula (Ctrl+Shift+Enter) in cell A12 and copy down

        =IFERROR(INDEX(Sheet1!$E$1:$DD$1,1,SMALL(IF(INDIRECT(“‘sheet1’!”&ADDRESS(MATCH(D$2,Sheet1!$A$1:$A$111,0),5)&”:”&ADDRESS(MATCH(D$2,Sheet1!$A$1:$A$111,0),108))=”Yes”,COLUMN(Sheet1!$E$1:$DD$1)-COLUMN(Sheet1!$D$1)),COUNTA(Position!A$11:A11))),””)

        Hope this helps.

        Reply

  • Hey there thanks for that…but I still get an error

    =INDEX(Sheet1!$E$1:$DD$1,1,SMALL(IF(#VALUE!(“‘sheet1’!”&ADDRESS(MATCH(D$2,Sheet1!$A$1:$A$111,0),5)&”:”&ADDRESS(MATCH(D$2,Sheet1!$A$1:$A$111,0),108))=”YES”,COLUMN(Sheet1!$E$1:$DD$1)-COLUMN(Sheet1!$D$1)),COUNTA(Position!A$11:A11)))

    Reply

  • =INDEX(Sheet!$F$1:$DD$1,1,SMALL(IF(INDIRECT(“‘Sheet1’!”&ADDRESS(MATCH(D$2,Sheet1!$A$2:$A$108,0),6)&”:”&ADDRESS(MATCH(D$2,Sheet1!$A$2:$A$108,0),108))=”YES”,COLUMN(Sheet1!$F$1:$DD$1)-COLUMN(Sheet1!$E$1)),COUNTA(Reports!A$11:A11)))

    has removed the error but resulting in 0’s being displayed

    Reply

    • Hi,

      In cell A11, enter this array formula (Ctrl+Shift+Enter) and copy down

      =IFERROR(INDEX(Sheet1!$E$1:$DD$1,1,SMALL(IF(INDIRECT(“‘sheet1’!”&ADDRESS(MATCH(D$2,Sheet1!$A$1:$A$111,0),5)&”:”&ADDRESS(MATCH(D$2,Sheet1!$A$1:$A$111,0),108))=”Yes”,COLUMN(Sheet1!$E$1:$DD$1)-COLUMN(Sheet1!$D$1)),COUNTA(A$9:A10))),””)

      Hope this helps.

      Reply

      • 🙁

        Nothing happens when I press ctrl shift enter

        Also nothing is happening in cell A11 … so I took out the error check and i’m still getting the same error as above. The error shows when I evaluate. When I get to the last evaluation….. I step in and it say’s the cell currently being evaluated contains a constant

        Reply

  • Hi Ashish – thanks for this informative article. It looks like the explanation of your array article at the top is broken. I want to do something similar to this but can’t figure out how to manipulate the formula.

    At the top of the sheet, I want to have a cell with a drop down of cars.

    Once a car is selected, I want to populate a list of all parts included in that specific car.

    I have a vlookup of car to car parts. The number of car parts to cars can change dynamically.

    Can you paste in your array formula letting me know which parts of the formula should point to which part of my query? Appreciate the help

    Reply

  • Hi Ashish Sir,

    Here I attached here a sheet in which we have Glasses model name with length, width, thickness and Category (Column A to E). Suppose one customer asked me to supply “TX4-G-AR” (J2) model but that model I don’t have in stock so to fulfil customer’s requirement I have to cut just next bigger Model in terms of length and width of “TX4-G-AR” to supply it as “TX4-G-AR” but category and thickness should be the same . So I manually selected those models and filled in K2:AN2, But my requirement is when I write any model in J2 then next 30 bigger models should come automatically in K2:AN2

    https://docs.google.com/spreadsheets/d/1iodBmaz2PWLExcXh15O4Rn_O7nb3PGjmMz-RgG6FOKI/edit#gid=1487928837

    Reply

  • I have been struggling with this for a few days – I am trying to populate column C.

    I want to do a look up based on two criteria:

    What ID is it?
    Does the date fall between a certain date range?

    The end result will be pulling the correct company and populating column C.

    Caveat here is that I am running into some size issues – this specific lookup would be on 30K rows in addition to the approx 90MB file I already have.

    I imagine it has to do with array and a few nested formulas but any help would be amazing!

    Any help would be much appreciated!

    https://docs.google.com/spreadsheets/d/16vB6eXhMbDnSy1C-N4lxgVCLXiYykApOZPiwYc-5KBs/edit?usp=sharing

    Reply

      • Thanks Ashish – this is super helpful! Do you know of any way to do this that doesn’t use index match. The actual workbook I am using has 30k rows so I have to run the update overnight.

        Reply

  • Hi,

    I have item number in column A, in column B – picture for this number. But some of the items have 4 pictures, and so there are rows with the same number but different picture. This table should look like: in column A – item number and in column B, C, D etc. to have the pictures. So do you have any idea to do it?
    Some friends gave me a formula which contains “MCONCAT” but this function doesn’t work with excel 2016.
    the formula is:
    =MCONCAT(OFFSET($B$1,MATCH(C2,$A$2:$A$8236,0),,COUNTIF($A$2:$A$8236,C2)),”,”)

    So please give me solution that doesn’t contain this formula “mconcat”

    Reply