Lookup with multiple criteria and get multiple results

jl2509 · Mar 16th 2018

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.

chirayuw · Mar 16th 2018

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]

jl2509 · Mar 16th 2018

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

jl2509 · Mar 16th 2018

Hi,

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

Thank you for you support.

**Ger Plante** · Mar 20th 2018

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

**Ger Plante** · Mar 20th 2018

Also updated thread title

jl2509 · Mar 20th 2018

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

**Ger Plante** · Mar 20th 2018

Cool... that works too

jl2509 · Mar 20th 2018

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

Lookup with multiple criteria and get multiple results

Participate now!

Share

Users Viewing This Thread