Vlookup multiple columns but with one column data only

[sureshmandadpu]

I have names in multiple columns which are unique. My requirement is vlookup any name from these columns and retrieve the value corresponding to that name. I have attached excel file.

Kindly guide me.

 

[bosco_yip]

In B12, copied down :

=INDEX(E$4:E$6,SUMPRODUCT((B$4:D$6=A12)*(ROW(B$4:B$6)-ROW($B$3))))

or, if you wanted to use VLOOKUP function, try this instead :

=VLOOKUP(A12,IF({1,0},INDEX(B$4:D$6,0,SUMPRODUCT((B$4:D$6=A12)*COLUMN($A1:$C1))),E$4:E$6),2,0)

Regards
Bosco

 

[sureshmandadpu]

In B12, copied down :

=INDEX(E$4:E$6,SUMPRODUCT((B$4:D$6=A12)*(ROW(B$4:B$6)-ROW($B$3))))

or, if you wanted to use VLOOKUP function, try this instead :

=VLOOKUP(A12,IF({1,0},INDEX(B$4:D$6,0,SUMPRODUCT((B$4:D$6=A12)*COLUMN($A1:$C1))),E$4:E$6),2,0)

Regards
Bosco

THANK YOU SIR..