Data Validation with respective numbers against it in Drop Down Menu

[Amit Punia]

In the attached file, there are Names and Marks. There are duplicate names in column A and Marks in Column B.

Data Validation is applied in Column E. So if we select "Amit" in Cell E2, then it should show all the marks in a drop down menu against Amit in Cell F2 and User can select any one value from that drop down menu.

Kindly help.

 

[Naresh Yadav]

Dear Amit,

pls see if this works for you.
I have used extra column(Highlighted in Grey Color) to fetch Marks based on the Name Selection

regards
Naresh

 

[vletm]

Something like this?

 

[Amit Punia]

Thanks a lot Naresh & Vletm for the help. Both the solutions are working absolutely fine.

 

[rahulshewale1]

HII Amit,

Please find solution .you can use index+aggregate formula also....

Regard
Rahul

 

[Naresh Yadav]

Dear Amit,

if your Name Range is always Sorted..like the way it is showing in your excel sheet
you can put this formula by going in Data validation>>Settings>>List>>and put the formula in source area without adding extra column
OFFSET($B$1,MATCH(E2,$A$2:$A$17,0),,COUNTIF($A$2:$A$17,E2),)

 

[Amit Punia]

@Rahul Index Aggregate formula is also working absolutely fine. Thanks a lot.
@naresh - I tried putting offset formula in Source, but it's not working. Anyways thanks for the first answer.

 

[Naresh Yadav]

Dear Amit,

Could you pls check the last file which i have shared to you
i have checked from my side and its giving the same result which is required to you
the only drawback in this, its showing repeated "Marks Value" if the "Marks value" are more than one time against a particular name

I m Again sharing you the excel Sheet


regards
Naresh