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. |
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. |
All times are GMT +1. The time now is 04:51 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com