I am looking for a formula to select the value on the left column

[celinesuzette]

Hi everyone,

I am looking for a formula to select the value on the left column

Here is an example of sheet. It represents the basket value of our customers. As you can see, Jaqueline & Pierre spent the same amount of money. Alphonse and Gertrude too.

In another tab, I used LARGE IF formula to automatically select the 4 highest basket value.

Problem is, I cannot use INDEX MATCH to find who are my best and beloved customers as it will only select the highest one.

As excell correctly identificate the 4 highest value, I guess it should be possible for him to give me the left cell value.

Any idea on how??
(please?)

Thanks in advance
Céline

 

[Fluff]

Hi & welcome to MrExcel.
How about

+Fluff New.xlsm
	A	B	C	D	E
1
2	Jaqueline	9900		Jaqueline	9900
3	Pierre	9900		Pierre	9900
4	Suzette	720		Alphonse	880
5	Alphonse	880		Gertrude	880
6	Gertrude	880
7
Main

Cell Formulas
Range	Formula
D2:E5	D2	=FILTER(A2:B6,B2:B6>=LARGE(B2:B6,4))
Dynamic array formulas.

 

[sandy666]

something like this?

Customer	Value		Value	Customer
Jaqueline	9900		9900	Jaqueline,Pierre
Pierre	9900		880	Alphonse,Gertrude
Alphonse	880		720	Suzette
Gertrude	880
Suzette	720

 

[sandy666]

correction

Value	Customer
9900	Jaqueline,Pierre
880	Alphonse,Gertrude

 

[Fluff]

If your data is not sorted you can use

+Fluff New.xlsm
	A	B	C	D	E
1
2	Suzette	720		Jaqueline	9900
3	Alphonse	880		Pierre	9900
4	Gertrude	880		Alphonse	880
5	Jaqueline	9900		Gertrude	880
6	Pierre	9900
7
8
Main

Cell Formulas
Range	Formula
D2:E5	D2	=SORT(FILTER(A2:B6,B2:B6>=LARGE(B2:B6,4)),2,-1)
Dynamic array formulas.