Xlookup not working when there are duplicate values?

[Mark McInerney]

Hi Folks - Thanks in advance for looking at this.

When there is a duplication in the sales column, the xlookup returns the first value every time - in the example below the "West" store is not appearing on my list when I use the xlookup formula - is there a way around this without me having to add a helper column to create unique sales values?

 

[hagia_sofia]

Would e.g. this do the trick:

=DROP(REDUCE("",UNIQUE(G6:G10),LAMBDA(a,b,VSTACK(a,FILTER(C6:C10,D6:D10=b)))),1)

 

[Cubist]

Try:

Book1
	A	B	C	D	E	F	G	H	I
1
2
3
4
5			Sales	Store		Rank	Sales		Store
6			North	2		1	2		North
7			South	7		2	2		West
8			East	8		3	5		Temp
9			West	2		4	7		South
10			Temp	5		5	8		East
Sheet7

Cell Formulas
Range	Formula
I6:I10	I6	=INDEX(FILTER(Sales[Sales],Sales[Store]=G6),COUNTIF($G$6:G6,G6))

 

[Fluff]

Another option

Fluff.xlsm
	A	B	C	D	E	F	G	H
1
2
3
4
5			Sales	Store		Rank	Sales	Store
6			North	2		1	2	North
7			South	7		2	2	West
8			East	8		3	5	Temp
9			West	2		4	7	South
10			Temp	5		5	8	East
Data

Cell Formulas
Range	Formula
G6:H10	G6	=CHOOSECOLS(SORT(C6:D10,2),2,1)
Dynamic array formulas.