Excel LOOKUP with multiple outcomes

Question

Currently I am using the formula: =LOOKUP("Pending", DK6, A6)

So if the cell contents of DK6 says “Pending” it displays the value from cell A6.

I need to add some more clauses and would appreciate some help on how to construct the formula. The logic I am looking for is as follows…

LOOKUP("Pending", DK6, A6) > ?ELSEIF? > LOOKUP(“Delegated”, DK6, leave cell blank) > ?ELSEIF? > LOOKUP(“Rejected”, DK6, leave cell blank) > ?ELSEIF? > LOOKUP(blank cell, DK6, leave cell blank)

I think maybe I need to use the IFERROR clause with my LOOKUPS but don't seem to be able to contract a formula that works! — In_Need_Of_Macro_Assistance

Unknown Unknown · Accepted Answer · 2014-09-16T21:19:17

To get lists of multiple matching rows you can apply SMALLL() to pick up the first, second, third, etc. value.

enter image description here

The formula in DL2 is,

=IFERROR(INDEX($A$2:$A$99, SMALL(INDEX(ROW($1:$98)+($DK$2:$DK$99<>"Pending")*1E+99,,),ROW(1:1))),"")
  ... or,
=IFERROR(INDEX($A$2:$A$99, SMALL(INDEX(ROW($1:$98)+($DK$2:$DK$99<>DL$1)*1E+99,,),ROW(1:1))),"")

To gather cells in column A where column DK is blank is a little different.

=IFERROR(INDEX($A$2:$A$99, SMALL(INDEX(ROW($1:$98)+(($A$2:$A$99="")+($DK$2:$DK$99<>""))*1E+99,,),ROW(1:1))),"")

These formulas seem to be a better fit than the array formulas provided in another thread. The values in column A are by nature unique so that does not have to be compensated for.

Excel LOOKUP with multiple outcomes

2 Answers