I have a list of entries in one Sheet

A                           B                                C

Location 1            Name1                        Boy

Location1              Name2                       Girl

Location2              Name1                     Boy

Location3              Name1                        Girl

Location3              Name2                        Girl

Location3              Name 3                       Boy

I am using a data validation in Sheet2, If i select the Location

I should be able to get the list of Names in one row.

Note: For certain locations there may be one entry and for few there might be around 10 entries.

I have around 200 entries like this with different locations

I have seen this.. But my qyery is like

I have location assorted in A column.

I am using this formula

={IF(ISERROR(INDEX('Product Lookup'!A:B,SMALL(IF('Product Lookup'!A:B=$A$2,ROW('Product Lookup'!A:B)),ROW('Product Lookup'!1:1)),2)),"",INDEX('Product Lookup'!A:B,SMALL(IF('Product Lookup'!A:B=$A$2,ROW('Product Lookup'!A:B)),ROW('Product Lookup'!1:1)),2))}

But it uses the Memory Utlization more.

Caluclating(4 processes..%) and it takes more time.

Wondering is there any other work around.

else i should use the Indirect Fun only i believe

HI,

I am not very clear about the issue. Did you mean that you want choose one location for example Location 1, then it will show Name 1 Name 2 in Sheet 2?

Try to insert a table for the data, then we can select the location which you want, then copy the names according the location filtered, then Paste Options -> Transpose in the sheet 2, it will paste the names in one row.

I am actually preparing a template.

Raw data will be one sheet ie,sheet1 and the other one is for the users who select the location on sheet2

So once there is drop down in sheet2 and they select any one location based on the drop down

Names should be displayed accordingly.

I dont want to copy or use filter as i am preparing a template

I use an array formula - entered using Ctrl-Shift-Enter - that can be copied down and across to extract all data associated with a key value in one column. I have assumed that the key column on your product lookup sheet is column A.  In any cell, array enter this

=IF(COUNTIF('Product Lookup'!$A:$A,$A$2)>=ROWS($A$1:A1),INDEX('Product Lookup'!A:A,LARGE(('Product Lookup'!$A$1:$A$999=$A$2)*ROW('Product Lookup'!$A$1:$A$999),COUNTIF('Product Lookup'!$A:$A,$A$2)-(ROWS($A$1:A1)-1))),"")

and copy down until it returns blanks, then across for as many columns as you have data on Product Lookup.