ISERROR(INDEX(SMALL with 2 or more criteria

[alainfranco]

Hello,

For the life of me, I can't figure this one out...

I am trying to search and return all records matching 2 criteria. This formula works perfectly for 1 criterion, but I am unable to get it a second one in there...

Working Formula for 1 criterion:

=IF($A$1="","",(IF(ISERROR(INDEX(Data!$A$1:$E$11,SMALL(IF(Data!$A$1:$A$11=$A$1,ROW(Data!$A$1:$A$11)),ROW(1:1)),1)),"",INDEX(Data!$A$1:$E$11,SMALL(IF(Data!$A$1:$E$11=$A$1,ROW(Data!$A$1:$E$11)),ROW(1:1)),1))))

Formula I am struggling with:

=IF($G$1="","",(IF(ISERROR(INDEX(Data!$A$1:$E$11,SMALL(IF(AND(Data!$A$1:$A$11=$G$1,Data!$B$1:$B$11=$G$2),ROW(Data!$A$1:$A$11)),ROW(1:1)),1)),"",INDEX(Data!$A$1:$E$11,SMALL(IF(Data!$A$1:$E$11=$G$1,ROW(Data!$A$1:$E$11)),ROW(1:1)),1))))

Is this even the correct way to string additional criteria?


I have a file ready with the data and formulas. Is there anyway for me to attach it to this post? Otherwise, here are the tabs and their content:


Data table:
https://ibb.co/jNDSmc

Query with 1 criterion. Successfully returns all matching records:
https://ibb.co/dxCbex

Same query, but on line 2:
https://ibb.co/eqJDsH

Query with 2 criteria that I am struggling with:
https://ibb.co/dM7nmc

Can anyone please point me in the right direction?

Thanks,

A.

 

[Marvinexcel]

Alternative Solution:

Paste the formula in G4 then drag all the way to required column and row.

=IF(ROWS(G$4:G4)>SUM(--(Data!$A$2:$A$11&Data!$B$2:$B$11=$G$1&$G$2)),"",INDEX(Data!A$2:A$11,SMALL(IF(Data!$A$2:$A$11&Data!$B$2:$B$11=$G$1&$G$2,ROW(Data!$A$2:$A$11)-ROW(Data!$A$2)+1),ROWS(G$4:G4))))

 

[Marvinexcel]

Don't forget to press ctrl+shift+enter for it is an Array.

Alternative Solution:

Paste the formula in G4 then drag all the way to required column and row.

=IF(ROWS(G$4:G4)>SUM(--(Data!$A$2:$A$11&Data!$B$2:$B$11=$G$1&$G$2)),"",INDEX(Data!A$2:A$11,SMALL(IF(Data!$A$2:$A$11&Data!$B$2:$B$11=$G$1&$G$2,ROW(Data!$A$2:$A$11)-ROW(Data!$A$2)+1),ROWS(G$4:G4))))

 

[alainfranco]

Alternative Solution:

Paste the formula in G4 then drag all the way to required column and row.

=IF(ROWS(G$4:G4)>SUM(--(Data!$A$2:$A$11&Data!$B$2:$B$11=$G$1&$G$2)),"",INDEX(Data!A$2:A$11,SMALL(IF(Data!$A$2:$A$11&Data!$B$2:$B$11=$G$1&$G$2,ROW(Data!$A$2:$A$11)-ROW(Data!$A$2)+1),ROWS(G$4:G4))))

That did it!

Thanks Marvin, I appreciate your help!

A.

 

[alainfranco]

Haha, yes, thanks

Hey, would you be so kind as to explain it in high level details, so that I can adapt it for other parts of the spreadsheet?

I could really use that help!

Thanks

 

[Marvinexcel]

Glad to help.

First, compare the data it meets your criteria.
For you to compare the data, join an array of data with ampersand symbol “&” to your criteria also joined by ampersand symbol “&”.
i.e. Data!$A$2:$A$11&Data!$B$2:$B$11=$G$1&$G$2. This formula will spit out this result:
{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE} - this is the position of string/value that meets the criteria. If you are to count, the TRUEs are on 7^th and 8^th position.

Now enclose it in IF statement to convert the TRUE into number/position: =IF(Data!$A$2:$A$11&Data!$B$2:$B$11=$G$1&$G$2,ROW(Data!$A$2:$A$11)-ROW(Data!$A$2)+1)

This expression “ROW(Data!$A$2:$A$11) “ will return the rows from $A$2:$A$11 which are 2,3,4,5,6,7,8,9,10,11, but we need to start with 1 so subtract the first row i.e. ROW(Data!$A$2) which is equivalent to 2 then the result now will be 0,1,2,3,4,5,6,7,8,9, but this is not what we want so we will add 1 back. Complete syntax will be “ ROW(Data!$A$2:$A$11)-ROW(Data!$A$2)+1 “. This expression will return the correct number starting from 1i.e. {1;2;3;4;5;6;7;8;9;10} – this will replace the TRUE with the correct position

From: {FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE}
To: {FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;7;8;FALSE;FALSE}

Enclose the IF statement to SMALL function to extract the value(7;8), and FALSE will be converted to #NUM ! error. SYNTAX will be :
=SMALL(IF(Data!$A$2:$A$11&Data!$B$2:$B$11=$G$1&$G$2, ROW(Data!$A$2:$A$11)-ROW(Data!$A$2)+1),ROWS(G$4:G4))

The expression “ ROWS(G$4:G4) ” will be the trigger for first small and second small. How mane rows are there (G$4:G4)? Answer is 1(first value), once you drag down, the first G$4 will be locked but the second G4 will move to G5 thus it will become (G$4:G5) which is now equivalent to 2(second value). And the resulting value will be 1^st small – 7 , second small – 8.

Now enclose the whole SMALL to INDEX to extract the resulting value/string. Complete syntax:

=INDEX(Data!A$2:A$11, SMALL(IF(Data!$A$2:$A$11&Data!$B$2:$B$11=$G$1&$G$2, ROW(Data!$A$2:$A$11)-ROW(Data!$A$2)+1),ROWS(G$4:G4)))

Index will now return the actual value/string based on the position that SMALL had spit out. i.e. First value in position 7 – “John” and so on.

Now enclose the whole INDEX to IFERROR(alternative solution) to return only the required information and remove the #NUM !.

=IFERROR(INDEX(Data!A$2:A$11,SMALL(IF(Data!$A$2:$A$11&Data!$B$2:$B$11=$G$1&$G$2,ROW(Data!$A$2:$A$11)-ROW(Data!$A$2)+1),ROWS(G$4:G4))),"")

Drag all the way to required cells then ENJOY… ��

Haha, yes, thanks

Hey, would you be so kind as to explain it in high level details, so that I can adapt it for other parts of the spreadsheet?

I could really use that help!

Thanks

 

[alainfranco]

Glad to help.

First, compare the data it meets your criteria.
For you to compare the data, join an array of data with ampersand symbol “&” to your criteria also joined by ampersand symbol “&”.
i.e. Data!$A$2:$A$11&Data!$B$2:$B$11=$G$1&$G$2. This formula will spit out this result:
{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE} - this is the position of string/value that meets the criteria. If you are to count, the TRUEs are on 7^th and 8^th position.

Now enclose it in IF statement to convert the TRUE into number/position: =IF(Data!$A$2:$A$11&Data!$B$2:$B$11=$G$1&$G$2,ROW(Data!$A$2:$A$11)-ROW(Data!$A$2)+1)

This expression “ROW(Data!$A$2:$A$11) “ will return the rows from $A$2:$A$11 which are 2,3,4,5,6,7,8,9,10,11, but we need to start with 1 so subtract the first row i.e. ROW(Data!$A$2) which is equivalent to 2 then the result now will be 0,1,2,3,4,5,6,7,8,9, but this is not what we want so we will add 1 back. Complete syntax will be “ ROW(Data!$A$2:$A$11)-ROW(Data!$A$2)+1 “. This expression will return the correct number starting from 1i.e. {1;2;3;4;5;6;7;8;9;10} – this will replace the TRUE with the correct position

From: {FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE}
To: {FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;7;8;FALSE;FALSE}

Enclose the IF statement to SMALL function to extract the value(7;8), and FALSE will be converted to #NUM ! error. SYNTAX will be :
=SMALL(IF(Data!$A$2:$A$11&Data!$B$2:$B$11=$G$1&$G$2, ROW(Data!$A$2:$A$11)-ROW(Data!$A$2)+1),ROWS(G$4:G4))

The expression “ ROWS(G$4:G4) ” will be the trigger for first small and second small. How mane rows are there (G$4:G4)? Answer is 1(first value), once you drag down, the first G$4 will be locked but the second G4 will move to G5 thus it will become (G$4:G5) which is now equivalent to 2(second value). And the resulting value will be 1^st small – 7 , second small – 8.

Now enclose the whole SMALL to INDEX to extract the resulting value/string. Complete syntax:

=INDEX(Data!A$2:A$11, SMALL(IF(Data!$A$2:$A$11&Data!$B$2:$B$11=$G$1&$G$2, ROW(Data!$A$2:$A$11)-ROW(Data!$A$2)+1),ROWS(G$4:G4)))

Index will now return the actual value/string based on the position that SMALL had spit out. i.e. First value in position 7 – “John” and so on.

Now enclose the whole INDEX to IFERROR(alternative solution) to return only the required information and remove the #NUM !.

=IFERROR(INDEX(Data!A$2:A$11,SMALL(IF(Data!$A$2:$A$11&Data!$B$2:$B$11=$G$1&$G$2,ROW(Data!$A$2:$A$11)-ROW(Data!$A$2)+1),ROWS(G$4:G4))),"")

Drag all the way to required cells then ENJOY… ��

Many thanks Marvin!

 

[alainfranco]

Glad to help.

First, compare the data it meets your criteria.
For you to compare the data, join an array of data with ampersand symbol “&” to your criteria also joined by ampersand symbol “&”.
i.e. Data!$A$2:$A$11&Data!$B$2:$B$11=$G$1&$G$2. This formula will spit out this result:
{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE} - this is the position of string/value that meets the criteria. If you are to count, the TRUEs are on 7^th and 8^th position.

Now enclose it in IF statement to convert the TRUE into number/position: =IF(Data!$A$2:$A$11&Data!$B$2:$B$11=$G$1&$G$2,ROW(Data!$A$2:$A$11)-ROW(Data!$A$2)+1)

T.....

Hi Marvin!

I've been playing around with your formula all day and it works great! Your explanations are spot on and made me learn new stuff! Thanks again! :D

In fact, it works so well that I have decide it to replace other similar formulas in my spreadsheet by yours. Im starting to wonder if I'll need to start paying you royalties every time I use the file! :D


Do you think it would be possible to make the formula loop through a list, and if 2 other criteria match a row, than it executes your formula, skips a line, and move on to the next match,where it runs your formula again.

In your opinion, do you think I need to use a macro to do this? Or can it be done through a formula?

Thanks,

Alan
Montreal, Canada