Combination of looking up a value in a given range with a certain criteria in excel

[Macro212]

Here is an example of what my dataset looks like in excel:

Column A Column B Column C Column D
Row 2 A B C
Row 3 1-1400 .25 .75 .50
Row 4 1400-1500 .50 .25 .30
Row 5 1501 .4 .5 .6
Row 6 1502-1700 .5 .6 .8


Rate Table

Range Category Rate
1300 A
1550 B
1300 B
1543 B


I essentially want to look up the number that is in the range column in the rate table, along with the associate category to find the appropriate rate. Im thinking it should be a combination of an index match statement but nothing seems to be working. Can you please help?

I.e. for the first one, my value would be .25, since it falls in the 1-1400 rate and is assigned a category A.

 

[Peter_SSs]

Welcome to the MrExcel board!

Just put the starting value for each range instead of the whole range in column A. (I have assumed the second range should actually start at 1401 since 1400 was already included in the first range.)
If you really want to see the end of the range too, put it in another column as I have done below.

Formula in C9 is copied down.

Excel Workbook
	A	B	C	D	E
1	Start	End	A	B	C
2	1	1400	0.25	0.75	0.5
3	1401	1500	0.5	0.25	0.3
4	1501	1501	0.4	0.5	0.6
5	1502	1700	0.5	0.6	0.8
6
7
8	Range	Category	Rate
9	1300	A	0.25
10	1550	B	0.6
11	1300	B	0.75
12	1543	B	0.6
Lookup rate

 

[Macro212]

Thank you so much! It worked great.

 

[Peter_SSs]

You’re welcome. Thanks for letting us know.