Index lookup with 2 lookup values and then return multiple results - array formula

Hi,

Please look at the small example I created. I have btw left error handling out deliberately in this example as just focusing purely on the point that I require help with here.

There are 2 dropdown boxes where you can select 1 of 2 values in each. I want to use an array formula to return a list of all the refs in column B where the values chosen in the 2 dropdown boxes match in columns C & D.

I can find them all based on 1 value so far like this:

{=INDEX($B$3:$D$12, SMALL(IF($G$2=$C$3:$C$12, ROW($C$3:$C$12)-MIN(ROW($C$3:$C$12))+1, ""), ROW(1:1)),1)}

I need to add to this to take the other dropdown box into account as well, I have tried with the AND function like this:

{=INDEX($B$3:$D$12, SMALL(IF(AND($G$2=$C$3:$C$12,$G$3=$D$3:$D$12), ROW($C$3:$C$12)-MIN(ROW($C$3:$C$12))+1, ""), ROW(1:1)),1)}

But clearly I am missing something here as this returns a #VALUE error.

Please show me the correct way to do this.

Many thanks

Attachment 395899
This formula worked for me in cell F8. Entered as an array

Please Login or Register  to view this content.

Thought it needed a different approach, very helpful thank you

This formula also works as an array. It's probably a little more beautiful because it doesn't just smash the columns together like the first formula did. It evaluates each column and then returns the rows where both criteria match. I learned this format from daffodil11.

Please Login or Register  to view this content.

All you really need is...

Please Login or Register  to view this content.