Need help finding formula to pull then rank rates based on separate column

[dallas1593]

Hi - hopefully can explain this properly. Basically, I am manually ranking choices from 1-3, but for choices 4 and on, I am looking for a formula that will look at Column A and know to only look at rates in Column D that are "1", and rank the lowest Rate as 4, then next lowest at 5, etc. - and then the same for Location 2 and so on. I have about 300 locations and 2800 lines, so a formula that could automate those Rate selections from 4 on but also know to only look at ranks within the specific Location Indicator would save hours.

So - ultimately, I want to fill Column C with Secondary Choices 4 and on with the lowest Rate in Column D but only for it's location as indicated in Column A.


A B C D

 

[felixstraube]

Is this what you need?:

rates.xlsx
	A	B	C	D
1	Location Indicator	Top Choice	Secondary Choices	Rate
2	1		5	105
3	1	1		107
4	1	2		112
5	1	3		115
6	1		6	127
7	1		4	100
8	2	3		105
9	2		6	107
10	2	2		112
11	2	1		115
12	2		7	127
13	2		4	100
14	2		5	102
15	3		4	105
16	3	1		107
17	3		5	112
18	3		6	115
19	3		7	127
20	3	2		100
21	3	3		102
Hoja1

Cell Formulas
Range	Formula
C2:C21	C2	=LET( Rates,SORT(FILTER(Hoja1!$D$2:$D$21,(Hoja1!$A$2:$A$21=Hoja1!$A2)*(Hoja1!$B$2:$B$21=0))), RatesSecChoice,HSTACK(Rates,SEQUENCE(ROWS(Rates),,4)), IFERROR(FILTER(CHOOSECOLS(RatesSecChoice,2),CHOOSECOLS(RatesSecChoice,1)=Hoja1!$D2),"") )

 

[felixstraube]

Same thing without the page reference:

rates.xlsx
	A	B	C	D
1	Location Indicator	Top Choice	Secondary Choices	Rate
2	1		5	105
3	1	1		107
4	1	2		112
5	1	3		115
6	1		6	127
7	1		4	100
8	2	3		105
9	2		6	107
10	2	2		112
11	2	1		115
12	2		7	127
13	2		4	100
14	2		5	102
15	3		4	105
16	3	1		107
17	3		5	112
18	3		6	115
19	3		7	127
20	3	2		100
21	3	3		102
Hoja1

Cell Formulas
Range	Formula
C2:C21	C2	=LET( Rates,SORT(FILTER($D$2:$D$21,($A$2:$A$21=$A2)*($B$2:$B$21=0))), RatesSecChoice,HSTACK(Rates,SEQUENCE(ROWS(Rates),,4)), IFERROR(FILTER(CHOOSECOLS(RatesSecChoice,2),CHOOSECOLS(RatesSecChoice,1)=$D2),"") )