Vlookup/Lookup/Index - want to apply on entire table

[smittal]

Dear team,

i am working on a excel dashboard where i need to do a certain operation:-

Grade Name1 Name2 Name3
A anul suresh ahima
B shweta X Y


now i have a db like which i want to fill up with grade. this table need data maping from above mentioned tabular. would request everyone please suggest for same.

grade
anul
suresh
ahmia
shweta


thanks in advance for same.

 

[Hui]

Smittal

Can you please post a sample file with a few rows of data and the format you want to get

 

[azumi]

=INDEX($A$2:$A$4;MAX(IF($B$2:$D$4=A7;ROW($A$2:$A$4)-ROW($A$2)+1)))

Array Formula

Pls see the file

Azumi

 

[shrivallabha]

You have not explained how your data is located.

Assuming below layout with data provided.

The first table [source data] is in Range A1:D3 and the second table [destination data] in E1:E5, try following formula in F2 which is based on assumption that the names are unique. And then copy down

=INDEX($A$1:$D$3,SUMPRODUCT(($A$1:$D$3=E2)*ROW($A$1:$D$3)),1)

 

[smittal]

Thanks all for your support.

Hui sir@ I am not able to upload file beacuse of Access Denied..
file upload by Azumi is perfect.
shrivallabha@ code is perfect but showing error when drag to other columns.

Thanks in advance for help.