Formula Forensics No. 003b – Lukes Reward – Part II

Posted on November 10th, 2014 in Formula Forensics , Huis , Posts by Hui - 53 comments

One of the more commonly asked questions on the Forums is “How Do I extract data from a list that matches a criteria“.

There are a number of ways to achieve this including use of Tables and Filtering, Pivot Table and Filtering, Advanced Filters and Formula based solutions. Ninja, Luke wrote his initial post in which he described a formula based approach in the Formula Forensic post


Of recent times though there has been a large number of questions at the forums “How Do I extract data from a list that matches a number of criteria

One such post was

This problem required the extraction of records that were within a range +/- a value from the base value. This requires two criteria Records >= Base – Value and Records <= Base + Value.

This post will look at a small modification to Lukes original Formula that allows such a solution to any number of criteria.


Recap of Lukes Formula

This post isn’t going to explain how Luke’s formula works and if you are interested I recommend that you revisit Luke’s discussion at:


Firstly, lets recap Luke’s original formula

=IF(COUNTIF(A:A,$D$2) < ROWS($E$2:E2), “”,  INDEX(B:B,  SMALL( IF($A$2:$A$10 =$D$2, ROW( $A$2:$A$10)), ROW(A1))))


The logic which allows the selection of the records is based inside the inner If() function as shown below

=IF(COUNTIF(A:A,$D$2) < ROWS($E$2:E2), “”,  INDEX(B:B,  SMALL( IF($A$2:$A$10 =$D$2, ROW( $A$2:$A$10)), ROW(A1))))

In addition the formula also checks that the number of records matching the criteria hasn’t been exceeded which results in an error. This occurs in the outer If() function.

=IF(COUNTIF(A:A,$D$2) < ROWS($E$2:E2), “”,  INDEX(B:B,  SMALL( IF($A$2:$A$10 =$D$2, ROW( $A$2:$A$10)), ROW(A1))))


To allow for multiple criteria we need to adjust both these sections.

As always with formula Forensics posts you can follow along using the attached Sample File (Excel 2007+ Only)

If you see an error on trying to open the sample file, accept it and proceed.


Adding Multiple Criteria

Firstly, lets add some extra data to Lukes data including a Month and Shop fields as well as a further reporting field Quantity


By adding multiple criteria we complicate the problem


So we need to search for records that match the Type, Month and Shop fields

Luke’s original formula was

=IF(COUNTIF(A:A,$D$2) < ROWS($E$2:E2), “”,  INDEX(B:B,  SMALL( IF($A$2:$A$10 =$D$2, ROW( $A$2:$A$10)), ROW(A1))))

The two areas which look after the logic of choosing records are shown in Pink and Red above


We can adjust this logic to suit our expanded data set and criteria using



Lets look at each section


This checks that the current record being reported ROWS($I$2:I2) is less than the number of records matching the criteria COUNTIFS($A:$A,$G$6,$B:$B,$G$9,$C:$C,$G$3).

It stops the #NUM! error from occurring if the formula tries to extract more records than those match the criteria

In our sample we can see that in the sixth row of the report, which is blank, the ROWS($I$2:I7) = 6 is greater than the number of records matching the criteria (5) and so a Blank cell is returned


The second change from Luke’s formula is ($A$2:$A$25=$G$6)+($B$2:$B$25=$G$9)+($C$2:$C$25=$G$3)=3

This is a simple addition of 3 Arrays, one for each criteria

If we go to cell I2 Edit the formula with F2, Then select the first criteria from the formula ($A$2:$A$25=$G$6) then press F9


If we repeat this for each criteria

Criteria 2: Select ($B$2:$B$25=$G$9) then press F9


Criteria 3: Select ($C$2:$C$25=$G$3) then press F9


The great things is that by combining the 3 criteria with simple Additions allows Excel converts the results to numbers, corresponding to how many criteria each record match

Combined criteria: Select ($A$2:$A$25=$G$6)+($B$2:$B$25=$G$9)+($C$2:$C$25=$G$3) then press F9

Excel returns: {1;1;2;2;1;2;0;0;1;1;0;1;2;2;3;3;2;3;1;1;3;3;1;2}

We can see that there are 5 values of 3, these correspond to the 5 Records that match all 3 Criteria

Finally comparing this array to the number of criteria we end up with an array of True/False which correspond to our records

Combined criteria: Select ($A$2:$A$25=$G$6)+($B$2:$B$25=$G$9)+($C$2:$C$25=$G$3) = 3 then press F9


Note the 5 True values, that correspond to the 5 records that match the 3 criteria.

As an array Formula accept the formula with Ctrl+Shift+Enter


We can now see that there are 5 records that match the 3 criteria.


This is used inside the Index() function to return Row number of the record that is required.

The function can then be copied across to return other fields as in the quantity using the same logic.


Final Discussion

Although there are a number of methods that can be used to solve this including Tables and Filtering, Pivot Table and Filtering and Advanced Filters, the use of a formula based approach allows the extraction of these records without any user intervention or VBA code.



You can download a copy of the above file and follow along: Download Here.


Formula Forensics “The Series”

This is the 31st post in the Formula Forensics series.

You can learn more about how to pull Excel Formulas apart in the following posts: Formula Forensic Series


Formula Forensics Needs Your Help

I need more ideas for future Formula Forensics posts and so I need your help.

If you have a neat formula that you would like to share like above, try putting pen to paper and draft up a Post like above or;

If you have a formula that you would like explained, but don’t want to write a post, send it to Hui or Chandoo.


53 Responses to “Formula Forensics No. 003b – Lukes Reward – Part II”

  1. XOR LX says:

    You can also exploit a technique used by VIkas Gautam to achieve the same results as the array:


    by using an identical COUNTIFS construction to that here, though with a "reversal" of the criteria and criteria_ranges (and here using entire column references would certainly not be advisable!), i.e.:


    Although I have not performed tests as to which of these two constructions is the more efficient (in terms of worksheet performance), I would imagine that the latter may well be.

    Also, a minor point, but the use of ROW(C1) for SMALL's k parameter leaves the construction susceptible to error upon row insertions. More rigorous is to use e.g. ROWS($I$2:I2) here also.


    • Michael (Micky) Avidan says:

      For the latter approach - may I suggest:
      Michael (Micky) Avidan
      “Microsoft® Answers” – Wiki author & Forums Moderator
      “Microsoft®” MVP – Excel (2009-2015)

  2. bob hoskins says:

    I'm an absolute array formula n00blet, and this might be a dumb question but I have to ask:

    Why do you need the 'small' function in this formula? Could you have a formula with the same functionality but without the 'small' function?

  3. Michael (Micky) Avidan says:

    After a short glance - it seems to me that the complete formula can be shorten as follows (in "Excel 2007" and above):
    Michael (Micky) Avidan
    “Microsoft® Answers" - Wiki author & Forums Moderator
    “Microsoft®” MVP – Excel (2009-2015)

  4. XOR LX says:

    This type of formula can almost always be shortened using an equivalent IFERROR set-up. However, what most people don't realise is that, if the dataset in question is relatively large (e.g. several thousand rows), the IFERROR version is generally vastly inferior - in terms of workbook performance - than the version which uses an initial IF clause, as given here by Luke.

    There's an explanation as to why here:

    Of course, if this is a "shortest" formula challenge then I too would go with the IFERROR set-up! But if and only if!


  5. Luke M says:

    Excellent add-on Hui. Thanks for explaining this concept. 🙂

  6. Jared says:

    How would you create the formula to leave out a certain criteria?

    If you wanted to search for both Type and Month but not include Shop as a filter as an example.

    • Michael (Micky) Avidan says:

      Leave out the part: *(B$2:B$25=G$9)
      Meaning - the formula will look like this:
      Michael (Micky) Avidan
      “Microsoft® Answers” – Wiki author & Forums Moderator
      “Microsoft®” MVP – Excel (2009-2015)

      • Jared says:


        Thanks but not exactly what I was looking for. Is there a way to make this dynamic so that you could have blank field in the drop down which would include ALL SHOP entries.

        So in this example you would get
        Type: Vegetable
        Month: January
        Shop: No Selection or Blank cell (Leaving blank would return both Shops in the North and South that fit the other criteria)

        Does that make sense?


        • Hui... says:

          As Micky suggested
          Change the *(B$2:B$25=G$9)
          to *if(G$9="",1,(B$2:B$25=G$9))

          for each section


          • Michael (Micky) Avidan says:

            1. You missed the G$9 (typed: "G$(" instead).
            2. Your formula "suffers" from a "few" extra brackets.
            Michael (Micky) Avidan
            “Microsoft® Answers” – Wiki author & Forums Moderator
            “Microsoft®” MVP – Excel (2009-2015)

  7. Temma says:

    This may seem like a silly question, but is there anyway to have the internal if statement search an array rather than a single cell reference. So search an array with an array of criteria?

    Basically, I am attempting to extract information based on user selected criteria. This means I might only have 1 item for "Line Item" but I could have 3 items for "WeekEnding", and I need to be able to pull all of the rows that have that "Line Item" for WeekEnding1, WeekEnding2, Weekending3, etc.. I have tried using an OR() but the formula just got angry.


  8. XOR LX says:


    For what you require, the OR function is not the appropriate choice within this type of array formula. Your construction for the WeekEnding criterion would be of the form:


    where I'm assuming that WeekEndingRange is the range reference pertaining to that data, e.g. C1:C1000, and that you have your values corresponding to WeekEnding1, WeekEnding2 and WeekEnding3 in A1:A3.

    This basically means that an entry in WeekEndingRange which matches ANY of WeekEnding1, WeekEnding2 and WeekEnding3 will be considered.

    If you don't want to use actual cells within the worksheet to hold your WeekEnding1, WeekEnding2 and WeekEnding3 values, you can hard-code them in the above construction itself. In that case, however, the syntax would be a little different, and to be sure of getting it right it would be useful to know the precise form that these values take (e.g. dates?).


    • Emily says:

      I am having the same question that I believe Temma asked, but am a bit confused on the response (or what "WeekEndRanges" refers to).

      Essentially my question is as follows: I have sets of account codes that are categorized (e.g. Salary, Tuition, supplies, etc), and I would like to pull values (Amt Paid and Description) from each of the codes but have them remain grouped under the categories I have delineated for each (E.g. Salary has acct codes 1051, 1052, 1054, 1771, etc). I would like to create a summary report based on all of the entries per account code but remain grouped still within the 'salary category'. Regarding the output, I don't need to know which account code it came from, but simply that the line item of 'Bob Smith $100.00' falls under the Salary categories.

      Hopefully this makes sense. Thank you for your help!

  9. Temma says:

    @XOR LX

    THANK YOU!!!! I knew I was missing something really simple and also knew that I had been staring at this spreadsheet for way too long to see it.

    This is perfect. Since I have all my ranges named and they are dynamic, this is the PERFECT solution!!!!

    Thank you again,

  10. XOR LX says:

    @Temma You're welcome! Glad to help!

  11. IainG says:


    I've tried this formula out today. I work with FOI requests, which are time limited, and want to produce a "to do" list of requests that are due a 10, 15, 17, 19 day reminder or are on day 20 and a response is due.
    I'm not as bothered about the error hiding bit, so have transposed the formula above to the following:

    To explain - My data is in a massive table on tab 2 (RequestLog), V is the column that gets a tick (P in wingding2) if the 10 day reminder has been sent, C is the column containing the reference number, AX is a running count of days remaining until deadline, AA is the column recording whether a final response has been sent.

    I've evaluated the steps, and everything is fine up until the last bit, rows 404 and 407 are returned, but the final production is #num (fyi - i did try the original with the error removing, but suspect i got the same answer as i couldn't see anything!)

    I know these kind of work, as i managed to formulate the following, which will show only the first instance of a reminder being due (but when copied down just shows the same!):

    • IainG says:

      Apologies, the formula displays wrong...

      =INDEX(RequestLog!$C$8:$C$1008,SMALL(IF((RequestLog!$AA$8:$AA$1008="")+(RequestLog!$V$8:$V$1008="")+(RequestLog!$AX$8:$AX$1008 LESS THAN 11)+(RequestLog!$AX$8:$AX$1008 GREATER THAN 5)=4,ROW(RequestLog!$C$8:$C$1008)),ROW(RequestLog!C7)))

  12. Alan says:

    Thanks very much for this, been looking for ages for something like this!

  13. DLOVE says:

    i am looking to match 2 fields to pull back 1 record for the field if unique. otherwise imore than 1 if multiple matches for criteria( in the same cell)

  14. arthur says:

    How can i make it work to where if i only wanted to select 2 out of the 3 criteria, and still show a result. Example: if I only selected the "month" and the "shop" can it show me all that match the month and shop even if the type is fruit of vegetable. OR select the all the items sold in January in the south shop, displaying all vegetables and fruits that meet the two criteria.

    In other words how can i make it so if i OPT to not choose one or more of the criteria, it still shows me data matching the criteria selected

  15. Granpa says:

    How do you handle blanks in your data? Suppose some of the lines in the sample were not region specific. You could enter the line twice, once with "south" and again with "north", but in my application I have several lines that have blanks so that is not practical. Is there something that I can put in the blank that will act like a wildcard?

  16. Priya K says:

    I’ve tried this formula out today.

    it works good but as soon as i am increasing cell range(in $A$2:$A$25) from 25 to 2500 it displays error. please help, i am really stuck here.

  17. Abhijeet says:

    Hi Luke

    Thanks for this excellent technique

    But i want to know if Multiple criteria & data is huge then how do we copy paste result in next sheet with help of macro.please give some VBA tips also

  18. rrk says:

    I want to extract data from different files from 1 folder:
    e.g all files will be saved in 1 location : \\c\files\

    I have desiged a summary but in the same format as indvidual files with a micro button. I want to write a code which extract information from indvidual files :\\c\files\ to the summary fromat that i have designed using the button

  19. pvdv says:


    I am working based on your sheet, but for my solution i want to get the output in an dropdown menu. For this reason, the formula needs to be placed in the data-validation menu.
    But when I do, i get only the first possible value as a return. How do I transform the formula to generate a comma seperated list (as the data validation requires)?
    Any help would be greatly appreciated.

    • Hui... says:


      Have you read about Dynamic Data Validation

      Jeff Weir is a gun on this and worth chasing him up

      • pvdv says:

        Thanks for the reply, but I am still not quite able to build what I want. I have one sheet consiting of 80 rows, each one with one driver. Name, drivers licence 1 (YN), drivers licence 2(YN), ... monday (YN), tuesday (YN) and finally a location from where the person is working from.

        Based on this data, i want to get the people able to ride routes in some sort of drop down list. There are around 70 routes to be driven, 7 days a week, with differences in required drivers licences, distribution centra and departure times. For maintainabillity i want to avoid listing options for each day a route is scheduled.

        It feels like i am almost there with your formula but I can't get the drop-down bit to work.

  20. sami says:

    i am extracting student data of a particular class having session "2015-16", and class "ONE" as CRITERIA from table wherein a particular record has 42 columns (i.e. class PG to 10)
    columns are: roll no, student name, father name, acadmic year, class, fee (last three columns are repeated)
    countifs is fetching no. of records but fail to fetch roll no wise record in my case. pls advise

  21. sami says:

    below mentioned data used as source:
    1, Saifullah, Samiullah, 2013-14, KG, 2400, 2014-15, ONE, 2500, 2015-16, TWO, 2500,
    2, EMAAN FATMAH, Roohullah, 2013-14, NURSERY, 1500, 2014-15, KG, 1500, 2015-16, ONE, 1500,
    3, muhammad aaqib, Samiullah, 2014-15, KG, 1800, 2015-16, ONE, 1900, 2016-17, TWO, 1900,
    4, Zareen Roohullah, Roohullah, 2010-11, NURSERY, 1700, 2011-12, KG, 1700, 2012-13, ONE, 1700,
    5, KALEEM, LATE VUSKA, 2013-14, KG, 2400, 2014-15, ONE, 2500, 2015-16, TWO, 2500,
    6, ZEESHAN TARIQ, 2013-14, NURSERY, 1500, 2014-15, KG, 1600, 2015-16, ONE, 1500,
    7, ABDULLAH, ASAD, 2014-15, KG, 1800, 2015-16, ONE, 1800, 2016-17, TWO, 1900,
    8, KASHIF, MUKHTAR, 2010-11, NURSERY, 1700, 2011-12, KG, 1700, 2012-13, ONE, 1700,

    ACADMIC YEAR 2013-14 (used as criteria)
    CLASS KG (used as criteria)
    LATE-CHG 100
    SLIP ISSUE DATE 28-04-2016
    FEE DUE DATE 11.05.2016
    SLIP VALIDITY DATE 20-05-2016

    what i want is to display roll nos matching the criteria?

    • Hui... says:


      I'd suggest asking the question at the Forums
      Please attach a sample file will allow us to give a specific answer

      The problem with the data you supplied is that there is no way to be sure that when I import it, that I get the columns/fields correct

  22. James says:

    Can you use less than or greater than? I have a formula that I'm trying to use dates and show data that fall within a certain time period.

    • Abhijeet says:

      Hi James

      what u want from 2 dates u want to identify which dates are falls or what can u pls upload data with expected result

    • Hui... says:


      something like:


  23. Glenn says:

    This is great. I have been searching for this solution. I am curious however, I am trying to use this with three criteria, two of which are dates. So essentially one criteria would be greater than or equal to one date and then less than or equal to another date (basically between two dates). How would I incorporate that into this? Thanks.

    • Hui... says:

      Something like:


  24. AALLeeXX says:


    Thanks for this interesting tutorial. It's almost what I was looking for, with a minor difference that is for the criteria I need to consider the capitalization, ie make "North" and "north" discriminated.
    I'm thinking of using FIND but I still fails using it with arrays.
    Is it possible ?


    • Hui... says:


      To use case sensitivity in the sample file for this tutorial

      I2: =IF((($A$2:$A$25=$G$6)+EXACT($B$2:$B$25,$G$9)+($C$2:$C$25=$G$3)) < ROWS($I$2:I2),"",INDEX(D:D,SMALL(IF(($A$2:$A$25=$G$6)+EXACT($B$2:$B$25,$G$9)+($C$2:$C$25=$G$3)=3,ROW($C$2:$C$25)),ROW(C1))))

      J2: =IF((($A$2:$A$25=$G$6)+EXACT($B$2:$B$25,$G$9)+($C$2:$C$25=$G$3)) < ROWS($I$2:J2),"",INDEX(E:E,SMALL(IF(($A$2:$A$25=$G$6)+EXACT($B$2:$B$25,$G$9)+($C$2:$C$25=$G$3)=3,ROW($C$2:$C$25)),ROW(D1))))

      You will also need to adjust the Data validation in G9

      You can download a copy of the modified file here:

      • AALLeeXX says:

        Hi Hui,

        Thank you for this great update, appreciated a lot.

        My case is however a bit more challenging 😉
        One of the criterias is rather "contains this string or substring" with case sensitivity. In our example above, the shops could contain 1 to 4 directions lise N, S, W, E, N-E, S-W but also n-e, s-W, etc.... then the criteria becomes "contains n" (like n, n-e, w-n... but not N, N-e...).
        This is why I was thinking of FIND which does that well in basic formulas, but with array, I'm stuck... Thaks for any other hint 😉

  25. Farhat says:

    Can we get unique(no duplicates) without blanks and alphabetically sorted list?

    In my case, i have a hierarchy with which i want to design a dashboard with dynamic charts for the staff reporting to a particular manager, for a particular month. It works fine, when we select a particular month. But, we also have "Quarters" on a different column, and when we select quarter, we get duplicated results.

    Any help shall be highly appreciated.

  26. sridhar says:

    For Multiple Criteria, we are getting all the data based on the selections that we have selected for Type, Month and Shop. Can you please help me to top n values with output shown as below

    Output Quantity
    Spinach 39
    Spinach 33
    Potato 29
    Broccoli 21

  27. HeyInKy says:

    Hi! great post I tried to take this down to only 2 criteria instead 3, and substitute table ranges, and ended up with this:


    ...where my array formula will be in column K, and my [Level] column is column E, but it returns #NUM! error in K@ before even copying down. The last portion, ROW(Table1[Level])),ROW(E1)))) seems to be the cause...

  28. Thiru says:

    I need to Extract The Records From Master data By using Multiple drop down Condition.
    Let me know.What Function i can Use?

  29. Greg says:

    I finally got it implemented and it works great so long as the changes to the criteria are TYPED into the cells. I have 3 criteria. Month, Year , and JV#. I would like JV# to be dynamic. But every time I use an index/match to determine the JV# of the data coming in, the data disappears I typically can't use the lookup again unless go back in excel to before I did the lookup . I can type a JV in and it will work. Further I can type more JV's in and they will work.

    Somehow data presented by a function will not trigger a lookup.

    Any ideas on how to fix this.

Leave a Reply