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

[TABLE="width: 389"]
<colgroup><col span="4"><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]Code[/TD]
[/TR]
[TR]
[TD]Start Weight[/TD]
[TD]End Weight[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]0.00[/TD]
[TD]0.8[/TD]
[TD]$5.08[/TD]
[TD]$5.55[/TD]
[TD]$5.13[/TD]
[TD]$5.17[/TD]
[/TR]
[TR]
[TD]0.81[/TD]
[TD]0.9[/TD]
[TD]$5.11[/TD]
[TD]$5.58[/TD]
[TD]$5.16[/TD]
[TD]$5.20[/TD]
[/TR]
[TR]
[TD]0.91[/TD]
[TD]1.4[/TD]
[TD]$5.23[/TD]
[TD]$5.81[/TD]
[TD]$5.28[/TD]
[TD]$5.32[/TD]
[/TR]
[TR]
[TD]1.41[/TD]
[TD]1.5[/TD]
[TD]$5.50[/TD]
[TD]$5.85[/TD]
[TD]$5.55[/TD]
[TD]$5.60[/TD]
[/TR]
</tbody>[/TABLE]

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).
[TABLE="width: 325"]
<colgroup><col span="4"><col></colgroup><tbody>[TR]
[TD]
[TABLE="width: 192"]
<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl67, width: 64"]Weight[/TD]
[TD="class: xl67, width: 64"]Code[/TD]
[TD="class: xl67, width: 64"]Rate[/TD]
[/TR]
[TR]
[TD="class: xl69"].95[/TD]
[TD="class: xl68"]A[/TD]
[TD="class: xl70, align: right"]$5.11 [/TD]
[/TR]
[TR]
[TD="class: xl69"].80[/TD]
[TD="class: xl68"]B[/TD]
[TD="class: xl70, align: right"]$5.55 [/TD]
[/TR]
[TR]
[TD="class: xl69"]1.50[/TD]
[TD="class: xl68"]B[/TD]
[TD="class: xl70, align: right"]$5.85 [/TD]
[/TR]
[TR]
[TD="class: xl69"].97[/TD]
[TD="class: xl68"]D[/TD]
[TD="class: xl70, align: right"]$5.32 [/TD]
[/TR]
[TR]
[TD="class: xl69"]1.10[/TD]
[TD="class: xl68"]C[/TD]
[TD="class: xl70, align: right"]$5.28 [/TD]
[/TR]
[TR]
[TD="class: xl69"].74[/TD]
[TD="class: xl68"]C[/TD]
[TD="class: xl70, align: right"] $5.13 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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

 

[Eric W]

Try:

	A	B	C	D	E	F
Code
Start Weight	End Weight	A	B	C	D

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0.8[/TD]
[TD="align: right"]$5.08 [/TD]
[TD="align: right"]$5.55 [/TD]
[TD="align: right"]$5.13 [/TD]
[TD="align: right"]$5.17 [/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]0.81[/TD]
[TD="align: right"]0.9[/TD]
[TD="align: right"]$5.11 [/TD]
[TD="align: right"]$5.58 [/TD]
[TD="align: right"]$5.16 [/TD]
[TD="align: right"]$5.20 [/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]0.91[/TD]
[TD="align: right"]1.4[/TD]
[TD="align: right"]$5.23 [/TD]
[TD="align: right"]$5.81 [/TD]
[TD="align: right"]$5.28 [/TD]
[TD="align: right"]$5.32 [/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]1.41[/TD]
[TD="align: right"]1.5[/TD]
[TD="align: right"]$5.50 [/TD]
[TD="align: right"]$5.85 [/TD]
[TD="align: right"]$5.55 [/TD]
[TD="align: right"]$5.60 [/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Weight[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Code[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Rate[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]0.95[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]A[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]$5.23 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]0.8[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]B[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]$5.55 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]1.5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]B[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]$5.85 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]0.97[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]D[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]$5.32 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]1.1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]C[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]$5.28 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]0.74[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]C[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]$5.13 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C10[/TH]
[TD="align: left"]=VLOOKUP(A10,$A$3:$F$6,MATCH(B10,$A$2:$F$2,0))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



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)))