VBA search array and return value

Please see attached excel file. The formula only works for one value - Q
and not for other values in column E
Thanks Carroll

Please Login or Register  to view this content.

UDF

Use in cell like
F5:

=LookUps(E5,$I$5:$K$9,3)

then fill down
To a Standard code module.

Please Login or Register  to view this content.

Does this array-entered** formula placed in cell F5 and copied down do what you want...

=IFERROR(INDEX(K:K,MAX(IF(E5=$I$5:$J$9,ROW(K$5:K$9),-1))),"")

**Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself