Index Match for multiple criteria and MAX

Hello, and many thanks to the experts here. I have searched the forums and Google for hours and can't come up with a formula that works for me. I'm sure it's a combination of INDEX/MATCH or something.

In the green fields below (column M), I need to insert my formula.

0001.PNG

It will reference data in another spot:

0002.PNG

This second range shows ratings based on cost scales. For Group 1, if the cost is $0.00 up to $1,000.00, the rating is 3.00. From $1000 to $10000, the rating is 2.50. You can see that $100,000 and beyond is rated 1.75 for group 1.

My formula needs to look in column L for the group, find that group in column O, and based on the cost in column K, get the rating from my reference area.

Please note that I'm dealing with a pretty large data set, and I can't use array formulas or my computer will explode.

The data in columns K,L,M is in a table in my real workbook, in case that matters.

Many thanks for the immense help that everyone always is! Please guide me if I've done something wrong for the forum rules; I'm new and tried to follow the posting guide.

Hi,

Would something like this work for you:

Excel 2016 (Windows) 64 bit

	K	L	M	N	O	P	Q
1	Cost	Group	Rating		Group	Min Scale	Rating
2	25	1	3		1	0	3
3	250000	1	1.75		1	1000	2.5
4	15000	1	2.25		1	10000	2.25
5	11000	3	1.75		1	100000	1.75
6	1234	3	2		2	0	2.5
7	5400	5	2.75		2	1000	1.5
8	110000	1	1.75		3	0	2
9	32456	3	1.75		3	10000	1.75
10	1	1	3		3	100000	1.5
11	75000	2	1.5		5	0	2.75
12	62000	6	2.15		6	0	2.15
13					7	0	1

Sheet: Sheet37

Excel 2016 (Windows) 64 bit

	M
2	=VLOOKUP(K2,CHOOSE(L2,P$2:Q$5,P$6:Q$7,P$8:Q$10,,P$11:Q$11,P$12:Q$12,P$13:Q$13),2,1)

Sheet: Sheet37

Formula copied down.