Return Value based on multiple criteria

[twilson4]

I am looking to return a value from a fixed column based on certain criteria being met. I have successfully done this using an Index Match formula but to get it to return the value from the correct month I have to hard code the rank value to a specific column and therefor it is not dynamic if/when I change the month.

Criteria 1: Look for a fixed value in a fixed column. (IE Look for "FL" in Column A:A)
Criteria 2: :
First determine which Column is being referenced based on referencing a value (IE 12 Columns Jan-Dec and need to reference the data in column "Feb")
Second, in that column look for a specific value (IE: 1)

Where "FL" and "1" line up, determine that row # and return it from a separate column B:B

Top Accounts by Month-State - 2022.xlsx
	D
6	ALBERTSONS WAREHOUSE
Southeast

Cell Formulas
Range	Formula
D6	D6	=IFERROR(INDEX(Data!$B:$B,MATCH(1,(Southeast!$B6=Data!$A:$A)*(Southeast!$C6=Data!$AY:$AY),0)),)

Named Ranges
Name	Refers To	Cells
Data!_FilterDatabase	=Data!$A$2:$BV$758	D6

 

[KRice]

I can't tell from your post where the different values are found in your sheet. If you want to do a two-way lookup and you know that there will only be one match on the row headings and one match on the column headings, you can do something like this:

MrExcel_20220728.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1		Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec		in col	in row	result
2	GA	14	18	82	58	74	49	75	14	66	40	43	25		Feb	FL	35
3	NC	51	53	65	30	63	50	65	14	25	53	89	24
4	SC	45	43	87	86	67	22	41	21	51	68	81	67
5	FL	87	35	14	38	48	62	52	92	38	58	89	53
6	VA	83	69	14	70	68	71	78	37	58	83	84	15
Sheet3

Cell Formulas
Range	Formula
Q2	Q2	=INDEX($B$2:$M$6,MATCH(P2,$A$2:$A$6,0),MATCH(O2,$B$1:$M$1,0))

 

[twilson4]

Kirk - thanks for your reply.

I created a mock up version of my data to see if it would be easier to get into the forum. I think, in reality, I am trying to do a 3 way look up. I am doing it right now in a two way look up but every time I want to look up the rank in a different month I need to manually change the month column I am referencing. Hoping there is a way to reference the rank values in more of an array so I can use one formula and it can then be dynamic depending on the month I am referencing.

Book12
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	State	AZ					THIS YEAR RANK
2	Month	Feb			Dist. STATE	Chains	Jan Rank	Feb Rank	Mar Rank	Apr Rank	May Rank	Jun Rank	Jul Rank	Aug Rank	Sep Rank	Oct Rank	Nov Rank	Dec Rank
3	Rank				AZ	CIRCLE K WAREHOUSE	1	1	1	2	6	5	1	1	1	1	1	1
4	1	CIRCLE K WAREHOUSE			AZ	WALGREENS	2	22	8	10	11	9	11	2	2	2	2	2
5	2	SAFEWAY			AZ	BEVMO!	3	4	5	11	9	7	10	3	3	3	3	3
6	3	FRYS MARKETPLACE			AZ	TOTAL WINE & MORE	4	3	2	4	2	3	5	4	4	4	4	4
7	4	FRYS MARKETPLACE			AZ	FRYS FOOD & DRUG STORE	5	9	6	9	13	14	13	5	5	5	5	5
8	5	WHOLE FOODS MARKET			AZ	FRYS MARKETPLACE	6	7	7	8	7	11	14	6	6	6	6	6
9					AZ	TRADER JOES	7	5	3	6	5	6	6	7	7	7	7	7
10					AZ	INDEPENDENTS	8	2	22	5	4	4	7	8	8	8	8	8
11					AZ	SAFEWAY	9	6	4	1	1	1	2	9	9	9	9	9
12					AZ	ALBERTSONS	10	10	9	3	3	2	3	10	10	10	10	10
13					AZ	WHOLE FOODS MARKET	11	8	10	7	8	8	8	11	11	11	11	11
14					AZ	AAFES	12	12	11	13	15	15	15	12	12	12	12	12
15					AZ	DELAWARE NORTH PARKS & RESORTS	13	11	12	14	14	12	12	13	13	13	13	13
16					AZ	WALMART DISTRIBUTION CENTER	14	13	13	15	16	16	16	14	14	14	14	14
17					AZ	SAMS CLUB	15	14	14	16	17	17	17	15	15	15	15	15
18					AZ	DOUBLETREE HOTEL	16	15	15	12	12	13	18	16	16	16	16	16
19					AZ	GOBOOZE	17	16	16	17	18	18	9	17	17	17	17	17
20					AZ	DEL SOL MARKET	18	17	17	18	19	10	19	18	18	18	18	18
21					AZ	BEVERAGE HOUSE	19	18	18	19	20	19	20	19	19	19	19	19
22					AZ	WESTIN HOTELS & RESORTS	20	19	19	20	10	20	21	20	20	20	20	20
23					AZ	HILTON GARDEN INN	21	20	20	21	21	21	22	21	21	21	21	21
24					AZ	GOPUFF	22	21	21	22	22	22	4	22	22	22	22	22
25					CO	INDEPENDENTS	1	1	1	1	1	1	1	1	1	1	1	1
26					CO	TOTAL WINE & MORE	2	2	3	3	2	2	2	2	2	2	2	2
27					CO	RIO GRANDE MEXICAN RESTAURANT	3	3	4	4	4	5	4	3	3	3	3	3
28					CO	RIDLEYS	4	4	5	5	5	4	5	4	4	4	4	4
29					CO	KING SOOPERS	5	5	2	2	3	3	3	5	5	5	5	5
30					CO	SAMS CLUB	6	6	6	6	6	6	6	6	6	6	6	6
Sheet1

Cell Formulas
Range	Formula
C4:C8	C4	=INDEX(G3:G30,MATCH(1,($F$3:$F$30=$C$1)*($I$3:$I$30=$B4),0))

 

[KRice]

Oh...I see. Since you are matching rank values, are there instances where you might have ties (say 2 chains tied for 3rd place)? Would you want both of them returned? What version of Excel are you using (update account details so that it displays with your name)...there may be an easier way to do this if you have Excel 365.

 

[twilson4]

Oh...I see. Since you are matching rank values, are there instances where you might have ties (say 2 chains tied for 3rd place)? Would you want both of them returned? What version of Excel are you using (update account details so that it displays with your name)...there may be an easier way to do this if you have Excel 365.

I have Office 365.

The issue of returning duplicate Ranks was the first issue I had to resolve earlier today. Using the below formula I was able to generate a rank and ensure there is a single value for each rank by state.

=COUNTIFS($A:$A,$A4,BR:BR,">"&BR4)+COUNTIFS($A$4:$A4,$A4,BR$4:BR4,"="&BR4)

 

[KRice]

Try this:

MrExcel_20220728.xlsx
	B	C			F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	State	AZ					THIS YEAR RANK
2	Month	Apr			Dist. STATE	Chains	Jan Rank	Feb Rank	Mar Rank	Apr Rank	May Rank	Jun Rank	Jul Rank	Aug Rank	Sep Rank	Oct Rank	Nov Rank	Dec Rank
3	Rank				AZ	CIRCLE K WAREHOUSE	1	1	1	2	6	5	1	1	1	1	1	1
4	1	SAFEWAY			AZ	WALGREENS	2	22	8	10	11	9	11	2	2	2	2	2
5	2	CIRCLE K WAREHOUSE			AZ	BEVMO!	3	4	5	11	9	7	10	3	3	3	3	3
6	3	ALBERTSONS			AZ	TOTAL WINE & MORE	4	3	2	4	2	3	5	4	4	4	4	4
7	4	TOTAL WINE & MORE			AZ	FRYS FOOD & DRUG STORE	5	9	6	9	13	14	13	5	5	5	5	5
8	5	INDEPENDENTS			AZ	FRYS MARKETPLACE	6	7	7	8	7	11	14	6	6	6	6	6
9					AZ	TRADER JOES	7	5	3	6	5	6	6	7	7	7	7	7
10					AZ	INDEPENDENTS	8	2	22	5	4	4	7	8	8	8	8	8
11					AZ	SAFEWAY	9	6	4	1	1	1	2	9	9	9	9	9
12					AZ	ALBERTSONS	10	10	9	3	3	2	3	10	10	10	10	10
13					AZ	WHOLE FOODS MARKET	11	8	10	7	8	8	8	11	11	11	11	11
14					AZ	AAFES	12	12	11	13	15	15	15	12	12	12	12	12
15					AZ	DELAWARE NORTH PARKS & RESORTS	13	11	12	14	14	12	12	13	13	13	13	13
16					AZ	WALMART DISTRIBUTION CENTER	14	13	13	15	16	16	16	14	14	14	14	14
17					AZ	SAMS CLUB	15	14	14	16	17	17	17	15	15	15	15	15
18					AZ	DOUBLETREE HOTEL	16	15	15	12	12	13	18	16	16	16	16	16
19					AZ	GOBOOZE	17	16	16	17	18	18	9	17	17	17	17	17
20					AZ	DEL SOL MARKET	18	17	17	18	19	10	19	18	18	18	18	18
21					AZ	BEVERAGE HOUSE	19	18	18	19	20	19	20	19	19	19	19	19
22					AZ	WESTIN HOTELS & RESORTS	20	19	19	20	10	20	21	20	20	20	20	20
23					AZ	HILTON GARDEN INN	21	20	20	21	21	21	22	21	21	21	21	21
24					AZ	GOPUFF	22	21	21	22	22	22	4	22	22	22	22	22
25					CO	INDEPENDENTS	1	1	1	1	1	1	1	1	1	1	1	1
26					CO	TOTAL WINE & MORE	2	2	3	3	2	2	2	2	2	2	2	2
27					CO	RIO GRANDE MEXICAN RESTAURANT	3	3	4	4	4	5	4	3	3	3	3	3
28					CO	RIDLEYS	4	4	5	5	5	4	5	4	4	4	4	4
29					CO	KING SOOPERS	5	5	2	2	3	3	3	5	5	5	5	5
30					CO	SAMS CLUB	6	6	6	6	6	6	6	6	6	6	6	6
Sheet5

Cell Formulas
Range	Formula
B4:B8	B4	=INDEX(SORT(FILTER($F$3:$S$30,$F$3:$F$30=$C$1),MATCH($C$2,LEFT($F$2:$S$2,3),0)),ROWS($B$4:$B4),MATCH($C$2,LEFT($F$2:$S$2,3),0))
C4:C8	C4	=INDEX(SORT(FILTER($F$3:$S$30,$F$3:$F$30=$C$1),MATCH($C$2,LEFT($F$2:$S$2,3),0)),ROWS($B$4:$B4),2)

 

[twilson4]

That worked....took a minute to get it transferred correctly to my actual working file...but got there. Thank you!!

 

[KRice]

That's great...glad to hear you got it working! I should have mentioned...by using the SORT function, if there are ties--hence multiple rows with the same rank--these formulas will return the top 5 rank values and the associated chain names. So rather than 1,2,3,4,5 you might get 1,2,3,4,4. You might investigate this to see if your approach for assigning different ranks to ties is necessary--you may want to leave the ties and then show them in the summary table.