# Name Variable LOOKUP to return multiple results

1. ## 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.

2. ## Re: Name Variable LOOKUP to return multiple results

Use ISNUMBER(SEARCH

In I13, with CTRL+SHIFT+ENTER,

=IFERROR(INDEX(A:A,SMALL(IF(ISNUMBER(SEARCH(H\$13,D\$3:D\$12)),ROW(D\$3:D\$12)),ROWS(I\$13:I13))),"")

then fill down.

3. ## Re: Name Variable LOOKUP to return multiple results

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.

4. ## Re: Name Variable LOOKUP to return multiple results

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

Thanks,

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1