Table lookup

[matt_ward]

Perhaps I have excel BLINDNESS today or something but im trying to do what i thought would be an easy lookup. I have a list that contains email aliases from the below table.

DSmith@gmail.com	Result1@gmail.com
Djones@gmail.com	Result2@gmail.com
DannyJones@gmail.com	Result3@gmail.com
JanDoe@gmail.com	Result4@gmail.com

What I want to do is lookup the items in the first column of the above list and return the First entry from the table below.

PrimaryEmail	Alias	Alias1	Alias2	Alias3	Alias4
Result1@gmail.com	DSmith@gmail.com	dave@gmail.com	Dave24@gmail.com
Result2@gmail.com	SKelp@gmail.com	sarah@gmail.com	sarahK@gmail.com	sarak@gmail.com
Result3@gmail.com	Djones@gmail.com	Danny@gmail.com	DannyJones@gmail.com
Result4@gmail.com	JDoe@gmail.com	Jane@gmail.com	JaneDoe@gmail.com	JanDoe@gmail.com	JD@GMail.com

Index and Match is the one I think I should be using here where the Aliases are the Range and the PrimaryEmail is the match. JUST NOT WORKING?! Any Help would be most appreciated.

 

[Cubist]

Which version of Excel are you using?

 

[matt_ward]

Which version of Excel are you using?

365 version 2409

 

[Cubist]

365 version 2409

Should update your profile. Try:

Book2
	A	B	C	D	E	F
1
2	DSmith@gmail.com	Result1@gmail.com
3	Djones@gmail.com	Result3@gmail.com
4	DannyJones@gmail.com	Result3@gmail.com
5	JanDoe@gmail.com	Result4@gmail.com
6
7	PrimaryEmail	Alias	Alias1	Alias2	Alias3	Alias4
8	Result1@gmail.com	DSmith@gmail.com	dave@gmail.com	Dave24@gmail.com
9	Result2@gmail.com	SKelp@gmail.com	sarah@gmail.com	sarahK@gmail.com	sarak@gmail.com
10	Result3@gmail.com	Djones@gmail.com	Danny@gmail.com	DannyJones@gmail.com
11	Result4@gmail.com	JDoe@gmail.com	Jane@gmail.com	JaneDoe@gmail.com	JanDoe@gmail.com	JD@GMail.com
Sheet1

Cell Formulas
Range	Formula
B2:B5	B2	=TOCOL(IFS($B$8:$F$11=A2,$A$8:$A$11),2)

 

[matt_ward]

Amazing... What is this new lookup magic i have to learn!?

 

[matt_ward]

Really Appreciate your help @Cubist Love the fact i now have a new favourite function!

 

[matt_ward]

@Cubist There is one problem though... i do have a large table and the calculations have brought my measly computer to its knees... Is there a more economical way of doing it??

 

[Cubist]

See if this is faster:

Book2
	A	B	C	D	E	F
1			Array
2	DSmith@gmail.com	Result1@gmail.com	Result1@gmail.com
3	Djones@gmail.com	Result3@gmail.com	Result3@gmail.com
4	DannyJones@gmail.com	Result3@gmail.com	Result3@gmail.com
5	JanDoe@gmail.com	Result4@gmail.com	Result4@gmail.com
6
7	PrimaryEmail	Alias	Alias1	Alias2	Alias3	Alias4
8	Result1@gmail.com	DSmith@gmail.com	dave@gmail.com	Dave24@gmail.com
9	Result2@gmail.com	SKelp@gmail.com	sarah@gmail.com	sarahK@gmail.com	sarak@gmail.com
10	Result3@gmail.com	Djones@gmail.com	Danny@gmail.com	DannyJones@gmail.com
11	Result4@gmail.com	JDoe@gmail.com	Jane@gmail.com	JaneDoe@gmail.com	JanDoe@gmail.com	JD@GMail.com
Sheet1

Cell Formulas
Range	Formula
C2:C5	C2	=INDEX($A$8:$A$11,ROUNDUP(XMATCH(A2:A5,TOCOL($B$8:$F$11))/COLUMNS($B$8:$F$11),0))
B2:B5	B2	=INDEX($A$8:$A$11,ROUNDUP(XMATCH(A2,TOCOL($B$8:$F$11))/COLUMNS($B$8:$F$11),0))
Dynamic array formulas.

 

[matt_ward]

Oh yes this is much better.

This one for the win...

B2:B5

B2

=INDEX($A$8:$A$11,ROUNDUP(XMATCH(A2,TOCOL($B$8:$F$11))/COLUMNS($B$8:$F$11),0))