Excel Look up one value and return multiple corresponding values

[mellisahi]

Sponsored Links

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.

[Macarenas]

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

=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))

[mellisahi]

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.

[chroma]

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

[amit shah]

Check out the below links to this will give you some idea how to do it.

Excel : vlookup multiple values
Help-How to select multiple values using lookup function???
Need help for vlookup function
How to use vlookup for rounding up numbers in Excel 2003

[Riyaz_Pasha]

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