Multiple results list from VLOOKUP

Hello,

I am struggling with creating a new list from a table...

Clipboard01.jpg

I can't figure out how to have the formula (C1) fill down to show a vertical list of Room Names which have not paid a Key Deposit. I just want a list of Room Names to be produced from any "Not Paid" in column A.

Can anyone help please?

Thanks!

Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands...) and some manually calculated results. Just before posting, scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again. Now follow the instructions at the top of that screen.

Thank you so much oeldere!

It worked for me...

But is there a way that you know of that can automatically create a list with formulas, that can be updated without clicking any filters etc...

Thanks!

Try this in C1:

=IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW($A$2:$A$20)/($A$2:$A$20=$L$1),ROWS($C$1:C1))),"")

Amazing, this worked a treat!!!! Thanks so much Phuocam...

One more question, If I wanted to include another value in the lookup - both Not Paid and Paid - and I put "Paid" in L2, is there a way to have the automated list that includes both values?

Yes, you can try this:

=IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW($A$2:$A$20)/(($A$2:$A$20=$L$1)+($A$2:$A$20=$L$2)),ROWS($C$1:C1))),"")