Left Vlookup on Certain Cell Values Only

[Ollie7957]

Hi all,

I am trying to produce a left index match formula that will produce a list of sites that are live only.

As per the attached spreadsheet, I am trying to produce a list like the one on the query tab, from the list in the database. Where each site is live, I need it to bring out the preceeding Number/Letter combo. In this example, I need it to bring out all those except for site 4, which is 'dead'

I hope someone can help. Thank you in advance!

P.S. I only realise now that I have posted this in the wrong section of the Forum, it should have been under formulas. I don't know how to change it, sorry!

[StephenR]

You can use an array formula such as this, in A1 and copy down

=IF(ROWS(A$1:A1)<=COUNTIF(Database!$C$1:$C$25,$B$1),INDEX(Database!$A$1:$A$25,SMALL(IF(Database!$C$1:$C$25=$B$1,ROW($A$1:$A$25)-ROW($A$1)+1),ROW())),"")

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

[Ollie7957]

Thank you StephenR. worked brilliantly.