Search

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

One of the more commonly asked questions on the Chandoo.org 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.

Chandoo.org Ninja, Luke wrote his initial Chandoo.org post in which he described a formula based approach in the Formula Forensic post http://chandoo.org/wp/2011/11/18/formula-forensics-003/

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

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: http://chandoo.org/wp/2011/11/18/formula-forensics-003/

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.

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

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

Lets look at each section

COUNTIFS(\$A:\$A,\$G\$6,\$B:\$B,\$G\$9,\$C:\$C,\$G\$3)<ROWS(\$I\$2:I2)

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

Excel returns: {TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE}

If we repeat this for each criteria

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

Excel returns: {FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE}

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

Excel returns: {FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE}

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

Excel returns: {FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE}

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.

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

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.

Hello Awesome...

My name is Chandoo. Thanks for dropping by. My mission is to make you awesome in Excel & your work. I live in Wellington, New Zealand. When I am not F9ing my formulas, I cycle, cook or play lego with my kids. Know more about me.

Thank you and see you around.

Related articles:

 Written by Hui... Tags: Formula Forensic, Formula Forensics Home: Chandoo.org Main Page ? Doubt: Ask an Excel Question

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

IF((\$A\$2:\$A\$25=\$G\$6)+(\$B\$2:\$B\$25=\$G\$9)+(\$C\$2:\$C\$25=\$G\$3)=3

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

IF(COUNTIFS(\$G\$6,\$A\$2:\$A\$25,\$G\$9,\$B\$2:\$B\$25,\$G\$3,\$C\$2:\$C\$25)

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.

Regards

• Michael (Micky) Avidan says:

For the latter approach - may I suggest:
IF((A\$2:A\$25=G\$6)*(B\$2:B\$25=G\$9)*(C\$2:C\$25=G\$3),...
Michael (Micky) Avidan
“Microsoft® Answers” – Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2015)
ISRAEL

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?

• Hui... says:

@Bob
Did you read Luke's original post, where this is described
http://chandoo.org/wp/2011/11/18/formula-forensics-003/

The small function returns the Nth row that matches the criteria
Initially it returns the 1st row that matches the criteria, then the 2nd , 3rd etc
The 1st match may be the 5th row of the source table etc
The Nth row is the last parameter in 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):
=IFERROR(INDEX(E:E,SMALL(IF((A\$2:A\$25=G\$6)*(B\$2:B\$25=G\$9)*(C\$2:C\$25=G\$3),ROW(C\$2:C\$25)),ROW(D1))),"")
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2015)
ISRAEL

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:

http://superuser.com/questions/812727/look-up-a-value-in-a-list-and-return-all-multiple-corresponding-values/812848#812848

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

Regards

5. Luke M says:

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

• Hui... says:

He he,
I answered about 5 posts in a 2 week period and figured I'd be better to do this once and then refer people to it.

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:
=IFERROR(INDEX(E:E,SMALL(IF((A\$2:A\$25=G\$6)*(C\$2:C\$25=G\$3),ROW(C\$2:C\$25)),ROW(D1))),””)
Michael (Micky) Avidan
“Microsoft® Answers” – Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2015)
ISRAEL

• Jared says:

Micky,

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?

Thanks!

• Hui... says:

@Jared
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

=IFERROR(INDEX(E:E,SMALL(IF(If(G\$6="",1,(A\$2:A\$25=G\$6))*If(G\$(="",1,(B\$2:B\$25=G\$9))*if(G\$3="",1,(C\$2:C\$25=G\$3)),ROW(C\$2:C\$25)),ROW(D1))),””)

• Michael (Micky) Avidan says:

@Hui,
1. You missed the G\$9 (typed: "G\$(" instead).
2. Your formula "suffers" from a "few" extra brackets.
=IFERROR(INDEX(E:E,SMALL(IF(IF(G\$6="",1,A\$2:A\$25=G\$6)*IF(G\$9="",1,B\$2:B\$25=G\$9)*IF(G\$3="",1,C\$2:C\$25=G\$3),ROW(C\$2:C\$25)),ROW(D1))),"")
Michael (Micky) Avidan
“Microsoft® Answers” – Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2015)
ISRAEL

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.

Thanks,
Temma

8. XOR LX says:

@Temma

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:

IF(ISNUMBER(MATCH(WeekEndingRange,A1:A3,0))

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

Regards

• 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!
Emily

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,
Temma

10. XOR LX says:

@Temma You're welcome! Glad to help!

11. IainG says:

Afternoon,

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:
{=INDEX(RequestLog!\$C\$8:\$C\$1008,SMALL(IF((RequestLog!\$AA\$8:\$AA\$1008="")+(RequestLog!\$V\$8:\$V\$1008="")+(RequestLog!\$AX\$8:\$AX\$10085)=4,ROW(RequestLog!\$C\$8:\$C\$1008)),ROW(RequestLog!C7)))}

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!):
{=INDEX(RequestLog!\$A\$8:\$AX\$1008,MATCH(1,(RequestLog!\$AA\$8:\$AA\$1008="")*(RequestLog!\$V\$8:\$V\$1008="")*(RequestLog!\$AX\$8:\$AX\$10085),0),2)}

• 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

• Hui... says:

@Arthur

Yes, this can be done by simply changing the selection logic
https://www.dropbox.com/s/78phxaej9ui3k7z/Formula-Forensics-3b_Mod2.xlsx?dl=0

In fact it is setup to work with 1, 2 or 3 criteria

Let me know what you think?

• Chris says:

What if you have North, South, East and West in your data and you add a field to select multiple locations (i.e. South and West)? I'm assuming you need to use the OR formula at some point, but having a few issues inserting that into the formula in the dropbox file. Thanks!

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.
=IF(COUNTIFS(\$A:\$A,\$G\$6,\$B:\$B,\$G\$9,\$C:\$C,\$G\$3)<ROWS(\$I\$2:I2),"",INDEX(D:D,SMALL(IF((\$A\$2:\$A\$25=\$G\$6)+(\$B\$2:\$B\$25=\$G\$9)+(\$C\$2:\$C\$25=\$G\$3)=3,ROW(\$C\$2:\$C\$25)),ROW(C1))))

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:

Hello,

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:

@Pvdv

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)
FEE PERIOD FROM MAY YEAR 2016
FEE PERIOD TO MAY YEAR 2016
LATE-CHG 100
SLIP ISSUE DATE 28-04-2016
FEE DUE DATE 11.05.2016
SLIP VALIDITY DATE 20-05-2016
SECTION A

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

• Hui... says:

@Sami

I'd suggest asking the question at the Chandoo.org Forums
http://forum.chandoo.org/
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:

@James

something like:

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

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:

@Glenn
Something like:

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

24. AALLeeXX says:

Hi,

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 ?

Thanks.

• Hui... says:

@Alex

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

• 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 😉

• Hui... says:

@Alex

I'm thinking a purpose built User Defined Function may be a better solution

Ask the question in the forums http://forum.chandoo.org/
and attach a sample file with sample answers

25. Farhat says:

Hi,
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.
Thanks

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 ...so I tried to take this down to only 2 criteria instead 3, and substitute table ranges, and ended up with this:

=IF(COUNTIFS(Table1[Region],\$H\$2,Table1[Level],\$H\$5)<ROWS(\$K\$2:K2),"",INDEX(Table1[Employee],SMALL(IF((Table[Region]=\$H\$1)+(Table[Level]=\$H\$5)=2,ROW(Table1[Level])),ROW(E1))))

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

30. Callao2908 says:

Good morning, thanks for such a good example.
What I want to know is that formula should be used,
so instead of seeing the list that includes vegetables
that are repeated, only the accumulated. Example:

Output Quantity
Potato 29
Spinach 72
Peas 19
Broccoli 21

Spinach It is the sum of 39 plus 33, the two that are
repeated, and so for all cases that repeat
Thanks for the attention
Jorge from Peru

• Hui... says:

@Callao2908

You can use the technique shown here to get the list of Vegetables
Then use a Sumifs() function to sum the Column where the vegetable is equal to eg: Potato

• Callao2908 says:

Thank you Hui
Regards

31. Sajeesh says:

Sir,

Consider, if we are having multiple products for Example Peas two times - how we will calculate.

Regards,

SAJEESH P

32. effefirn says:

I am looking ways to extract data by single formula (copy-pasteable all the way down) for this case:
Suppose the data is:

Apple
Orange
Broccoli
Spinach
Pear
Peas
Apple
Orange
Broccoli
Spinach
Pear
Peas
Apple
Mango
Potato
Spinach
Pear
Peas
Apple
Orange
Broccoli
Spinach
Pear
Peas

Now, we know there are 4 Apples. and 3 Orange. So I want to extract the data into

Apple
Apple
Apple
Apple
Orange
Orange
Orange

Anyone has idea?

• Chandoo says:

Sorting the data?

• ron maltase says:

Wonderful tutorial. Learned a lot.

But I seem to fail at modifying it to my problem which is, list all those names ( in column A), whose salaries ( in column B) are BETWEEN a number in D1 and D2. Your help would be greatly appreciated.

• Hui... says:

@Nisar

Try:
=IF(COUNTIFS(\$B:\$B,">"&\$D\$1,\$B:\$B,"<"&\$D\$2)\$D\$1)+(\$B\$2:\$B\$500<\$D\$29)=2,ROW(\$C\$2:\$C\$500)),ROW(C1)))) If that doesn't work, please post the question in the Forums https://chandoo.org/forum/
and attach a sample file so we can check the solution

34. faecal says:

One problem i have is if you have blanks cells it will return a zero. Is there a way to return a blank if its a true blank cell or some other code (#N/A) if it finds a true blank cell? The data could also have legitimate 0's as well as blank cells.

 « CP025: Sexy on spreadsheet, Ugly on Printout Looking up when data won’t play nice – few more alternatives »

Get FREE Excel & Power-BI Newsletter

One email per week with Excel and Power BI goodness. Join 100,000+ others and get it free.
{"found_math": false, "script_math_tex": 0, "script_math_asciimath": 0, "math_annotations": 0, "math_alttext": 0, "mathml": 0, "mathjax_tag": 0, "mathjax_inline_tex": 0, "mathjax_display_tex": 0, "mathjax_asciimath": 0, "img_math": 0, "codecogs_latex": 0, "wp_latex": 0, "mimetex.cgi": 0, "/images/math/codecogs": 0, "mathtex.cgi": 0, "katex": 0, "math-container": 0, "wp-katex-eq": 0, "align": 0, "equation": 0, "x-ck12": 0, "texerror": 0, "url": "https://chandoo.org/wp/formula-forensics-no-003b-lukes-reward-part-ii/", "fetch_time": 1708471300000000000, "content_mime_type": "text/html", "warc_filename": "crawl-data/CC-MAIN-2024-10/segments/1707947473347.0/warc/CC-MAIN-20240220211055-20240221001055-00573.warc.gz", "warc_record_offset": 183522809, "warc_record_length": 53295, "token_count": 8573, "char_count": 29121, "score": 3.671875, "int_score": 4, "crawl": "CC-MAIN-2024-10", "snapshot_type": "longest", "language": "en", "language_score": 0.869266927242279, "file_path": "/sailhome/yjruan/project/latent-lm/data/processed/finemath-4plus/finemath-4plus/train-00042-of-00064.parquet"}