Excel Xlookup with Mode (to return the most frequent result)

[Knockoutpie]

Is there a way to do this?

I'm aware Xlookup will return the first result it finds, but what If i want to return the most frequent result?

If the results are what appears below, how do i return Apple?

Apple
Apple
Pear
Banana
Orange
Apple

Example of Formula
=XLOOKUP(A1,A:A,B:B)

But, if i try
=MODE(XLOOKUP(A1,A:A,B:B))
Result is #N/A

 

[Fluff]

How about

=INDEX(A2:A100,MODE(IFNA(MATCH(A2:A100,A2:A100,0),"")))

 

[Knockoutpie]

How about

=INDEX(A2:A100,MODE(IFNA(MATCH(A2:A100,A2:A100,0),"")))

I think this is close, but it returns the first result, not the most frequent.

 

[Fluff]

No, It returns the most frequent result.

 

[Knockoutpie]

No, It returns the most frequent result.

You are correct, however it looks like I need to modify a little bit..

I'm trying to return the most frequent result in B, using the value in C and matching with A..

 

[Peter_SSs]

Like this?

23 05 09.xlsm
	A	B	C	D
1	Pear	Green
2	Pear	Brown	Pear	Brown, Yellow
3	Pear	Brown
4	Apple	Brown
5	Pear	Brown
6	Pear	Green
7	Pear	Yellow
8	Pear	Yellow
9	Pear	Yellow
Mode

Cell Formulas
Range	Formula
D2	D2	=LET(f,FILTER(B1:B9,A1:A9=C2),TEXTJOIN(", ",,INDEX(f,MODE.MULT(MATCH(f,f,0)))))