Thread: Function to get multiple values

Hi Everybody,

I am new to excel and need help on writing fuctions

I have table

A--------B---------C
Store1/Item1/Amt
Store1/Item2/Amt
Store1/Item3/Amt
Store1/Item4/amt
Store2/Item2/Amt
Store2/Item3/Amt
Store2/Item4/amt
Store3/Item1/Amt
Store3/Item2/Amt
Store3/Item3/Amt
Store3/Item4/amt

when list value is changed for Store, I need formula to select all the Items in the store

since the items list is different.

For eg. For Store1
Values Returned to column E
Item1
Item2
Item3
Item4

I tried Vlookup but getting Item1 for the values since Store1 first match is Item1

Thanks for any suggestion

thnaksforhelp,

Attached is an example workbook based on the sample data you provided. Cell E2 contains a dropdown list of the unique values in column A (used advanced filter -> copy to range I1 -> unique values only -> column I is hidden). Cell F2 contains the following formula:
=IF(COUNTIF(A:A,$E$2)>ROW()-2,INDEX(B:B,MATCH($E$2,A:A,0)+ROW()-2),"")

The ROW()-2 is -2 becase the formula starts in row 2 (cell F2). Adjust as needed. Then the formula is copied down 10 cells. Only items for the selected store are shown. Note that this solution requires that identical stores be in consecutive rows (as is the case in your sample data).

Hope that helps,
~tigeravatar