lookup to return multiple data (Words) in a dropdown menu

[Chippy]

Can I create a drop down menu by looking up a table to find a certain product and returning the values from multiple columns

i.e. look up a product in a lookup table and return the values (being words) from columns 4,5 and 6 and making them avaialable in a drop down menu.


I have tried using Vlookup but i don't know if it will return multiple selections. I also tried using Offset which returned multiple selections but i didn't see how to look up a table for a product to strt with.

 

[Luke M]

You could do it with a dynamic named range. Assuming your lookup table looks something like this (Product is in cell A2):

Product    Col 4   Col 5    Col6

a           1        2        3

b           4        5        6

etc.

Named range (let's call it "MyRange") formula is:

=OFFSET($A$2,MATCH(Product_to_lookup_cell,$A$3:$A$10,0),1,1,3)


This will return an 1x3 array of cells, with the row offset being controlled by the MATCH function. Not sure where the cell with first product selection is, you need to fill that in. Setup your dropdown (data validation?) to use a custom list, defined as:

=MyRange

Assuming you input a proper product, dropdown should work.