Matrix Lookup (finding a match using two criterion)

[evilrammy]

Hi Team,

Would you please help me develop an index match or some type of lookup formula that has the following:

Lookup table looks like this

		Code
Start Weight	End Weight	A	B	C	D
0.00	0.8	$5.08	$5.55	$5.13	$5.17
0.81	0.9	$5.11	$5.58	$5.16	$5.20
0.91	1.4	$5.23	$5.81	$5.28	$5.32
1.41	1.5	$5.50	$5.85	$5.55	$5.60

<colgroup><col span="4"><col><col></colgroup><tbody>
</tbody>

And then find the rates using the weight and code. (I've filled in the rate manually, that's what I'm trying to find with a formula).

Weight Code Rate .95 A $5.11 .80 B $5.55 1.50 B $5.85 .97 D $5.32 1.10 C $5.28 .74 C $5.13 <colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody> </tbody>

<colgroup><col span="4"><col></colgroup><tbody>
</tbody>

I appreciate any and all help on this. Thank you very much! David

 

[Eric W]

Try:

	A	B	C	D	E	F
1			Code
2	Start Weight	End Weight	A	B	C	D
3	0	0.8	$5.08	$5.55	$5.13	$5.17
4	0.81	0.9	$5.11	$5.58	$5.16	$5.20
5	0.91	1.4	$5.23	$5.81	$5.28	$5.32
6	1.41	1.5	$5.50	$5.85	$5.55	$5.60
7
8
9	Weight	Code	Rate
10	0.95	A	$5.23
11	0.8	B	$5.55
12	1.5	B	$5.85
13	0.97	D	$5.32
14	1.1	C	$5.28
15	0.74	C	$5.13
16

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula C10 =VLOOKUP(A10,$A$3:$F$6,MATCH(B10,$A$2:$F$2,0)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Check your first entry, I got a different result.

 

[Aladin Akyurek]

Book1
	A	B	C	D	E	F	G	H	I	J
1	Start Weight	End Weight	A	B	C	D		Weight	Code	Rate
2	0	0.8	$5.08	$5.55	$5.13	$5.17		0.95	A	$5.11
3	0.81	0.9	$5.11	$5.58	$5.16	$5.20		0.8	B	$5.55
4	0.91	1.4	$5.23	$5.81	$5.28	$5.32		1.5	B	$5.85
5	1.41	1.5	$5.50	$5.85	$5.55	$5.60		0.97	D	$5.32
6								1.1	C	$5.28
7								0.74	C	$5.13
8
Sheet1

In J2 enter and copy down:

=LOOKUP(H2,$A$2:$A$5,INDEX($C$2:$F$5,0,MATCH(I2,$C$1:$F$1,0)))