# Thread: Excel Look up one value and return multiple corresponding values

1. Member
Join Date
Jan 2009
Posts
58

## Excel Look up one value and return multiple corresponding values

I have created a excel file which contains names and contact number of more then 5000 people along with there pin code number which many of them are repeated i want to I want to look up all the repeated values against a single value i mean to say the when i lookup for any one value it should return me multiple corresponding values respectively.

2. Member
Join Date
May 2008
Posts
4,834

## Excel Look up one value and return multiple corresponding values

Try to use the following formula hope it works for you

using advance filter take out unique names in Col A paste it in Col C

C2 you have names in D2 put this formula and drag it down

Code:
```=IF(ISERROR((INDEX(\$B1\$1:\$B1\$100,SMALL(IF(\$A\$1:\$A\$100=\$C\$2,ROW(\$A1\$1:\$A1
\$100)),ROW(1:1)),0))),"",INDEX(\$B1\$1:\$B1\$100,SMALL(IF(\$A1\$1:\$A\$100=\$C
\$2,ROW(\$A1\$1:\$A1\$100)),ROW(1:1)),0))```

3. Member
Join Date
Jan 2009
Posts
58

## Excel Look up one value and return multiple corresponding values

I have try to use the above formula but it's not working for me is there any other method which i can use it and thanks for replying me.

4. Member
Join Date
Apr 2008
Posts
2,139

## Lookup Two Values In A List And Return Multiple Corresponding

Try to use the below steps hope it works for you.

1. Select any cell within the source table
2. Then click on Data , then Pivot table, Click twice Next
3. Now wizard would get open over here click on Layout,
4. Over here Drag and drop "Name" into ROW area and Drag and drop "number" into DATA area 3 times, one below the other leave the 1st as Sum.
5. Now Doubleclick on the 2nd- - >select Max under Summarize by- - > click on OK
6. Double-click on the 3rd- - >select Min under Summarize by- - > Click on OK
7. Finally click on OK and finish

5. Member
Join Date
Dec 2008
Posts
101

6. Member
Join Date
Oct 2009
Posts
1

## Re: Excel Look up one value and return multiple corresponding values

Hi
I have a data that has a particular number in sheet 1 and the database in in the second sheet, I need to know how many times payment has been made and amounts to be listed in cells next to the number

#### Posting Permissions

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