Vlookup right to left and get first and last name colum

[satish78]

Hi Friends,

Here is the data which I am trying to vlookup but did get a formula to that

[TABLE="width: 1254"]
<colgroup><col span="2"><col><col><col span="12"><col><col span="3"></colgroup><tbody>[TR]
[TD]ColumnA[/TD]
[TD] ColumnB
[/TD]
[TD]ColumnC[/TD]
[TD] ColumnD
[/TD]
[TD] ColumnE
[/TD]
[TD]ColumnF[/TD]
[TD]ColumnG[/TD]
[TD]ColumnH[/TD]
[TD]ColumnI[/TD]
[TD]ColumnJ[/TD]
[TD]ColumnK[/TD]
[TD]ColumnL[/TD]
[TD]ColumnM[/TD]
[TD]ColumnN[/TD]
[TD]ColumnO[/TD]
[TD]ColumnP[/TD]
[TD]ColumnQ[/TD]
[TD]ColumnR[/TD]
[TD]ColumnS[/TD]
[TD]ColumnT[/TD]
[/TR]
[TR]
[TD]Ray[/TD]
[TD]Chu[/TD]
[TD]R[/TD]
[TD]C[/TD]
[TD]@co.westmoreland.pa.us[/TD]
[TD]Ray.Chu@co.westmoreland.pa.us[/TD]
[TD]RChu@co.westmoreland.pa.us[/TD]
[TD]Ray.C@co.westmoreland.pa.us[/TD]
[TD]R.Chu@co.westmoreland.pa.us[/TD]
[TD]Chu.Ray@co.westmoreland.pa.us[/TD]
[TD]Chu.R@co.westmoreland.pa.us[/TD]
[TD]C.Ray@co.westmoreland.pa.us[/TD]
[TD]RayChu@co.westmoreland.pa.us[/TD]
[TD]RayC@co.westmoreland.pa.us[/TD]
[TD]ChuRay@co.westmoreland.pa.us[/TD]
[TD]ChuR@co.westmoreland.pa.us[/TD]
[TD]CRay@co.westmoreland.pa.us[/TD]
[TD="colspan: 3"]Lacey.Peterson@wvjc.com[/TD]
[/TR]
[TR]
[TD]Deanna[/TD]
[TD]Gunlefinger[/TD]
[TD]D[/TD]
[TD]G[/TD]
[TD]@drinkerbiddle.com[/TD]
[TD]Deanna.Gunlefinger@drinkerbiddle.com[/TD]
[TD]DGunlefinger@drinkerbiddle.com[/TD]
[TD]Deanna.G@drinkerbiddle.com[/TD]
[TD]D.Gunlefinger@drinkerbiddle.com[/TD]
[TD]Gunlefinger.Deanna@drinkerbiddle.com[/TD]
[TD]Gunlefinger.D@drinkerbiddle.com[/TD]
[TD]G.Deanna@drinkerbiddle.com[/TD]
[TD]DeannaGunlefinger@drinkerbiddle.com[/TD]
[TD]DeannaG@drinkerbiddle.com[/TD]
[TD]GunlefingerDeanna@drinkerbiddle.com[/TD]
[TD]GunlefingerD@drinkerbiddle.com[/TD]
[TD]GDeanna@drinkerbiddle.com[/TD]
[TD="colspan: 3"]Williams.Jemile@norristown.org[/TD]
[/TR]
[TR]
[TD]Jemile[/TD]
[TD]Williams[/TD]
[TD]J[/TD]
[TD]W[/TD]
[TD]@norristown.org[/TD]
[TD]Jemile.Williams@norristown.org[/TD]
[TD]JWilliams@norristown.org[/TD]
[TD]Jemile.W@norristown.org[/TD]
[TD]J.Williams@norristown.org[/TD]
[TD]Williams.Jemile@norristown.org[/TD]
[TD]Williams.J@norristown.org[/TD]
[TD]W.Jemile@norristown.org[/TD]
[TD]JemileWilliams@norristown.org[/TD]
[TD]JemileW@norristown.org[/TD]
[TD]WilliamsJemile@norristown.org[/TD]
[TD]WilliamsJ@norristown.org[/TD]
[TD]WJemile@norristown.org[/TD]
[TD="colspan: 3"]Gunlefinger.Deanna@drinkerbiddle.com[/TD]
[/TR]
[TR]
[TD]Lacey[/TD]
[TD]Peterson[/TD]
[TD]L[/TD]
[TD]P[/TD]
[TD]@wvjc.com[/TD]
[TD]Lacey.Peterson@wvjc.com[/TD]
[TD]LPeterson@wvjc.com[/TD]
[TD]Lacey.P@wvjc.com[/TD]
[TD]L.Peterson@wvjc.com[/TD]
[TD]Peterson.Lacey@wvjc.com[/TD]
[TD]Peterson.L@wvjc.com[/TD]
[TD]P.Lacey@wvjc.com[/TD]
[TD]LaceyPeterson@wvjc.com[/TD]
[TD]LaceyP@wvjc.com[/TD]
[TD]PetersonLacey@wvjc.com[/TD]
[TD]PetersonL@wvjc.com[/TD]
[TD]PLacey@wvjc.com[/TD]
[TD="colspan: 3"]RayC@co.westmoreland.pa.us[/TD]
[/TR]
</tbody>[/TABLE]


I want to vlookup and find a match of ColumnR within a Column Range F:Q and return first and last name of that row in ColumnS and ColumnT

Thanks
Satish

 

[Eric W]

Put this array formula in S2:

=IFERROR(INDEX(A:A,SMALL(IF($F$2:$Q$5=$R2,ROW($F$2:$Q$5)),1)),"not found")

change the ranges to match your sheet, then confirm by pressing Control+Shift+Enter. Then copy to T2. Then drag S2:T2 down as far as needed.

 

[satish78]

Great Eric.
Thanks for making my task easy.

 

[Eric W]

You're welcome.