Lookup

J

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.
 
L

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top