Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup
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. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Joe" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
LOOKUP FUNCTION? (LOOKUP VALUE BEING A TIME RENERATED FROM A FORMU | Excel Discussion (Misc queries) | |||
Lookup looks to the prior column if zero appears in the lookup col | Excel Discussion (Misc queries) | |||
Matrix lookup/mulitple criteria lookup | Excel Discussion (Misc queries) | |||
Join 2 Lists - Lookup value in 1 list & use result in 2nd lookup | Excel Worksheet Functions | |||
Sumproduct - Condition based on lookup of a Lookup | Excel Discussion (Misc queries) |