Question on returning Value from a chart

[USChad]

I am having trouble coming up with a formula or way to return a value from a chart. I have messed around with SUM PRODUCT but can not figure out a way to use it effectively. I have attached an example of the chart I am trying to use. What I would like to return is the value in the spreadsheet that falls between the set of criteria. What I would have on a separate spreadsheet would be an increase and an average. I would like to return the cell amount that fits the criteria. For example in the chart if the average is 55 and the increase is 10% i would like it to return $1200. So if it falls between the two values i would like it to use the lower amount. Any help would be appreciated.

Thanks in advance.

AVERAGE
Increase 50 60 70
0.00% $- $200 $400
1.50% $200 $400 $600
3.00% $400 $600 $800
4.50% $600 $800 $1,000
6.00% $800 $1,000 $1,200
7.50% $1,000 $1,200 $1,400
9.00% $1,200 $1,400 $1,600
10.50% $1,400 $1,600 $1,800

[mhuston]

First, I suggest you use the term "table" so you do not confuse readers with the graphic display "chart". Second, I am unclear about your use of Sheet 2. It looks like you want a new table using midpoints of both the average and increase amounts? If so why not just insert a column using 55, 65. 75. etc?

[USChad]

Sorry if I was not very clear. No.. what I really want is to return the dollar value based on a given average and growth. For instance if I have a person that has an avg of 66 and an increase of 2% i would like to return the value of $400. If they would have averaged over 70 then the value returned would have been $600.

Hope this is clearer.

[ChemistB]

Do you want to round down both the average and the % increase? If your Average was 59 and 10.3% increase, you'd still want 1200 as opposed to 1400?

Assuming Average in C5, % increase in D5, then use the INDEX and MATCH formulas like so

Please Login or Register  to view this content.

Does that work for you?

[USChad]

yes sir that is correct...round both down

[mhuston]

I indexed to a row and column match to arrive at the amount.