Trying to create a mini rating system

[LtCmdrData]

Hi,

I hope someone can help or at least point me in the right direction. I am trying to create a what I'll call a mini rater. I want it to work like this: The user is prompted to enter a state from a drop down list. Next the user enters a weight that they want calculated. I have two columns of data behind the scenes. I need to find the row where the users weight falls between the values in the two columns. Next it looks to the right and finds the rate associated with the two columns or bracket. Finally it multiplies the weight entered by the user times the rate and returns a value. Below is an example of the data. Should I even be trying this in Excel? Or is this better suited for Access?

Del St	Act Lbs	Rate		delstate	minW	maxW	rate
AL	2400	47665		AL	0	999	45.82
				AL	1000	1999	33.00
				AL	2000	2999	19.86
				AL	3000	3999	16.09
				AL	4000	4999	10.68
				AR	0	999	43.48
				AZ	2000	2999	28.99
				CA	0	999	104.17
				CA	1000	1999	62.98
				CO	0	999	92.67
				CT	6000	6999	13.80
				CT	9000	9999	12.78
				CT	10000	10999	12.44
				FL	0	999	87.07
				FL	1000	1999	29.75
				FL	2000	2999	28.77
				GA	0	999	31.32
				GA	2000	2999	22.35

<colgroup><col width="64" style="width: 48pt;" span="8">
<tbody>






























































































































































































</tbody>

 

[njimack]

Confirm with ctrl+shift+enter:

=INDEX(Table1[rate],MIN(IF(Table1[delstate]=A2,IF(Table1[maxW]>=B2,ROW(Table1[delstate])-1))))

 

[cunningAce]

Give this formula a try

=SUMPRODUCT((A2=E2:E19)*($B$2>=F2:F19)*($B$2<=G2:G19)*(H2:H19))*$B$2

A2 is user state input cell
B2 is user weight input cell

 

[LtCmdrData]

Thank you Neil. That accomplished what I was trying to do. If you don't mind could you explain in plain English how the formula works? I'm still new to these nested formulas and such.

 

[njimack]

Thank you Neil. That accomplished what I was trying to do. If you don't mind could you explain in plain English how the formula works? I'm still new to these nested formulas and such.

The MIN function looks at all rows where [delstate] is equal to A2 and [maxW] is greater than or equal to B2. It then takes the row number (minus 1 to allow for the headers in row 1) and uses this in the INDEX formula to return the correct record from the [rate] field.

Hope that helps!