Lookup

[Joe]

How do I have Excel return multiple values from a lookup in a table of
information? For example, if I have excel do a vlookup or an index/match to
the input in A1. Say A1 contains the word "Cat". I want excel to return all
the values in a table that correspond to "Cat", not just the first value it
finds.

 

[Luke M]

Here's an array** formula that might work:

=INDEX(B:B,SMALL(IF($A$2:$A$7="cat",ROW($B$2:$B$7)),ROW(A1)))

This assumes id value is in A2:A7, return value is in B2:B7. Note that the
index column should be the same as the column of return values. Drag down as
needed.

**Array formulas must be confirmed using Ctrl+Shift+Enter, not just Enter.