Xlookup help

[zinah]

Hi,

I have below table that has duplicate values for each EEID and what I need is to map those EEID to another table that has business and home for each EEID, when I use the xlookup the formula brings one the first value, as you see in below table and what I need is to get the expected result, can anyone help please?

Book5
	A	B	C	D	E	F	G
1	EEID	Current Formula	Expected Result			EEID	Type
2	4000	Business	Business			4000	Business
3	4000	Business	Home			4000	Home
4	5000	Business	Business			5000	Business
5	5000	Business	Home			5000	Home
6	6000	Business	Business			6000	Business
7	6000	Business	Home			6000	Home
8	7000	Business	Business			7000	Business
9	7000	Business	Home			7000	Home
10	8000	Business	Business			8000	Business
11	8000	Business	Home			8000	Home
Sheet1

Cell Formulas
Range	Formula
B2:B11	B2	=XLOOKUP(A2,F:F,G:G)

 

[Fluff]

How about

=INDEX($G$2:$G$11,AGGREGATE(15,6,(ROW($G$2:$G$11)-ROW($G$2)+1)/($F$2:$F$11=A2),COUNTIFS(A$2:A2,A2)))

 

[zinah]

How about

=INDEX($G$2:$G$11,AGGREGATE(15,6,(ROW($G$2:$G$11)-ROW($G$2)+1)/($F$2:$F$11=A2),COUNTIFS(A$2:A2,A2)))

As usual, you keep on suggesting and sharing great formulas! Thank you so much, that worked great

 

[Fluff]

Glad to help & thanks for the feedback.