Using a key to return multiple values

[polarisking]

Say my key is ANIMAL, and I have an array


Machine Car

Machine Stove

Machine Train

Animal Dog

Animal Fish

Animal Squirrel

Animal Cat

Animal Weasel

House Bungalow

House Cape Cod


I want to specify the key value and return all the values associated with it. Vlookup returns just the first one. I'd like to see something like


Animal

- Dog

- Fish

- Squirrel

- Cat

- Weasel

 

[SirJB7]

Hi, polarisking!

To see something alike I'd recommend you a visit to the nearest zoo

Regards!

 

[Trader273]

Could mess around with formulas, but easier and quicker to just use a pivot table with a slicer or filter.

 

[kaushik03]

Hi, polarisking,


Assuming your data ranges from A1 to B10 and E1 contains the word Animal.


At E2, write the below formula:


=IFERROR(INDEX($B$1:$B$10,SMALL(IF($A$1:$A$10=$E$1,ROW($A$1:$A$10),""),ROW(A1))),"") press CTRL+SHIFT+ENTER from keyboard to enter it as an array formula and drag it down as needed.


Also see this file here:

http://speedy.sh/JjDmY/LOOKUPP.xlsx


Kaushik

 

[inbp]

Hi polarsiking

 

[inbp]

you can search on this chandoo site by searching 2nd value vlook up


chandoo.org/wp/2010/11/10/vlookup-second-value/


i uploaded the file which chandoo already shared


https://www.dropbox.com/s/6b0jthq9327zmj2/vlookup-2nd-value.xls


it is best to use


Regards

Muhammad Shakeel

 

[polarisking]

Thanks to all for your quick and elegant responses.