Index Match help

LTTFMark

New Member
Joined
Oct 9, 2013
Messages
2
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>
 

Some videos you may like

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
Joined
Oct 12, 2006
Messages
44,061
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
Joined
May 7, 2011
Messages
3,699
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
 

Watch MrExcel Video

Forum statistics

Threads
1,113,823
Messages
5,544,538
Members
410,619
Latest member
gregor222
Top