# Lookup with multiple criteria and get multiple results

• Hi All,

I need a bit of help again to fix an array formula.
Sample worksheet attached.

Basically, I cannot get my array formula to pull the correct details from one worksheet.

## Files

• I used a helper column in the sheet to figure out what to vlookup. you can always change font colour to white so its not visible

[ATTACH]n1201090[/ATTACH]

## Files

• Hi Appreciate the response but this is not really what I am looking for.

There are still suppliers being listed and I only Want "Contract Owners and Main Contractors" from the Store Details Tab, along with their current Status "Active Only"
The sample file I submitted in the OP shows the list output as it should be if the array formula I was playing with, was correct.

Thanks

• Hi,

I resolved the issue with my original array formula and I get the results I need.

Thank you for you support.

• Hi JL2509, I would be interested in seeing what you came up with for a solution. Please find attached one approach from me. Array Formula:

=IFERROR(INDEX(\$C\$3:\$C\$15,LARGE(((\$F\$3:\$F\$15="Contract Owner")+(\$F\$3:\$F\$15="Main Contractor"))*(\$G\$3:\$G\$15="Active")*ROW(\$F\$3:\$F\$15)-2,ROW(1:1))),"")

Regards
Ger

## Files

_______________________________________________
There are 10 types of people in the world. Those that understand Binary and those that dont.

Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...

_______________________________________________

_______________________________________________
There are 10 types of people in the world. Those that understand Binary and those that dont.

Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...

_______________________________________________

• Hi Ger,

I used a helper column and =IFERROR(INDEX('Store Details'!\$C\$3:\$C\$15,SMALL(IF('Store Details'!\$H\$3:\$H\$15=1,ROW('Store Details'!\$C\$3:\$C\$15)-ROW('Store Details'!C\$3)+1),ROWS(B\$23:B23))),"")
as the array formula, as shown in the updated attachment.

thank you

## Files

• Cool... that works too

_______________________________________________
There are 10 types of people in the world. Those that understand Binary and those that dont.

Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...

_______________________________________________

• Thanks, as does your solution
Still learning here and everyday is a new headache day :hammerhe:

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!