Two conditions in an array IF statement

[hassanm]

Hi,

I currently have an IF statement that selects certain data in a whole array of data but at the moment the equation filters the data based on one condition (Determinand Name (A1). I would like to adjust it so that it includes another statement, Location. I have tried to use IF(AND but that did not work. Any advice?

Please Login or Register  to view this content.

Ideally I would like it to be separated into locations i.e. location A and then all the results associated. Please see attachment FYI

Thanks

[JBeaucaire]

Here's one way:

=IFERROR(INDEX(Data!A:A,SMALL(IF($A$1&"-"&$B$1=INDEX(Data!$C$2:$C$4740&"-"&Data!$B$2:$B$4740, 0),ROW(Data!$C$2:$C$4740)),ROWS(A$4:A4))),"")

[peterrc]

I have replicated what you already have with a Pivot Table (no formulas required).
If you just want to see the results for just "A" then use the Location filter to just show results for A.
Please see attached.
Is this what you are after ?

Regards

peterrc