# Index Match help

#### LTTFMark

##### New Member
Need some help, I need a formula that will index/match the costs for the state and weight brackets in the table.
If I enter CA (B8) and 2000 (B9) for the weight the formula should choose "\$.16" in B10.
Since the weight brackets are a range I could not figure the formula to work with it. I could add a row for the weights if needed to create a range if that makes sense.
Thank you

 A B C D E F G H State 1-499 500-999 1000-1999 2000-4999 5000-9999 10000-19999 20000+ 1 AL \$ 1.02 \$ 0.50 \$ 0.40 \$ 0.34 \$ 0.30 \$ 0.26 \$ 0.23 2 AR \$ 0.88 \$ 0.52 \$ 0.41 \$ 0.33 \$ 0.27 \$ 0.22 \$ 0.19 3 AZ \$ 0.68 \$ 0.32 \$ 0.29 \$ 0.23 \$ 0.16 \$ 0.13 \$ 0.11 4 CA \$ 0.48 \$ 0.20 \$ 0.20 \$ 0.16 \$ 0.10 \$ 0.07 \$ 0.05 5 CO \$ 0.80 \$ 0.41 \$ 0.36 \$ 0.29 \$ 0.22 \$ 0.19 \$ 0.16 6 CT \$ 1.06 \$ 0.62 \$ 0.49 \$ 0.39 \$ 0.31 \$ 0.28 \$ 0.25 7 8 State CA 9 Weight 2,000 10 result \$ 0.16

<tbody>
</tbody>

### Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

#### Jonmo1

##### MrExcel MVP
Welcome to the board.

If you change the values in Row1 to just the low end values...
1 500 1000 2000,etc...

=INDEX(\$B\$2:\$H\$7,MATCH(B9,\$A\$2:\$A\$7,0),MATCH(B10,\$B\$1:\$H\$1))

B9 = State to find within A2:A7
B10 = Weight to find within B1:H1

#### markmzz

##### MrExcel MVP
Maybe this:

Code:
``=LOOKUP(\$B10,--LEFT(SUBSTITUTE(\$B\$1:\$H\$1,"-",REPT(" ",4)),5),INDEX(\$B\$2:\$H\$7,MATCH(\$B9,\$A\$2:\$A\$7,0),))``

Markmzz

#### LTTFMark

##### New Member
Thank you Jonmo1 and markmzz, both formulas worked.

Replies
4
Views
49
Replies
5
Views
124
Replies
1
Views
278
Replies
1
Views
108
Replies
5
Views
286