Vlookup Filtered Array

[freespace28]

Hi All,

I am new to the forum and have don't know if this is even possible.

I have a spreadsheet that I need to look up values based on a certain criteria.


Location| Name |Status |Paid
CA | Tom |Close |YES
CA | Jerry |Open |NO
AZ | Will |Close |YES
AZ | Sam |Open |NO
NY | Frank |Close |NO
NY | Lisa |Open |NO
WA | Amy |Close |YES
WA | John |Open |NO


Result should be this if I lookup the location for only open orders and return the paid column.


Lookup |Paid
CA |NO
AZ |NO
NY |NO
WA |NO

sorry not sure the best way to paste excel tables.

I have tried other examples on this forum in which I find too difficult to adapt to my situation.

filter-array-example.xlsx

[daffodil11]

I don't believe a single cell can accomplish all that you're asking for. It can be done, however, with a series of formulas.

You can do it by putting the following formula in A14:

=IFERROR(INDEX(A$2:A$9,SMALL(IF($C$2:$C$9="OPEN",IF(MATCH($A$2:$A$9&$B$2:$B$9&$C$2:$C$9&$D$2:$D$9,$A$2:$A$9&$B$2:$B$9&$C$2:$C$9&$D$2:$D$9,0)+ROW($A$1)=ROW($A$2:$A$9),ROW($A$2:$A$9)-ROW($A$1))),ROWS(I$2:I2))),"")

(This formula is entered as an array using Ctrl+Shift+Enter).

Then copy down and over as needed.

The only other option I know of would be to use a macro, which would likely duplicate the efforts of copying the same formula across the range and then copying the value.