Name Variable LOOKUP to return multiple results

I am using Excel 2007.

I have a workbook with multiple sheets. Sheet1 is my lookup page, Sheet2 is my data. I have attached an example. I was given this by co-worker thinking it would return multiple entries, but only comes up blank.


=IF(COUNTIF($A$3:$A$11,$A$3)<ROW(A3),"",INDEX(D$3:D$11,SMALL(IF($D$3:$D$11=$H$13,ROW($A$3:$A$11)),ROW(A3))))

My goal is to search similiar names or variations of a name to return all the LOC (Col A) options.

When I use this formula I figures out, it only returns the last one found: =LOOKUP(2^15,SEARCH(H13,D3:D11),A3:A11)

Can anybody assist this newbie?

Thanks.

Haseeb A,

Thanks for the revision. Your recommendation works great for creating new rows, but my space is limited for new row creation. It may be too difficult to concatenate the results into one cell, so is it possible to have the formuala copied over to new columns to work? I tried by changing the ROW and ROWS to COLUMN and COLUMNS, but that didn't work. Any suggestions?

Thanks.

I will just have another sheet to create rows and then just use the =column to put in a new column.

Thanks,