Two Way lookup with exact and approximate match

[Intermedius]

Hello,

I am trying to figure out a two lookup that will lookup two columns of data, one with an exact match and the other with an approximate match. i.e.

My issue, is that when i deal with thousands of Materials, with different scale sets, I pickup the wrong scales with Scale lookup 1. SO My objective is to pickup the approximate scale according to that specific material.

Scale Lookup 1 =INDEX($I$5:$I$40,MATCH(D6,$I$5:$I$40,1))

Scale Lookup 2 = =INDEX($I$5:$I$40,MATCH(D5,$I$5:$I$40,1),MATCH(B5,$J$5:$J$40,0))

[TABLE="width: 831"]
<tbody>[TR]
[TD]Pstng Date[/TD]
[TD]Material[/TD]
[TD]Purchase Order[/TD]
[TD]PO Quantity[/TD]
[TD]Scale Lookup 1[/TD]
[TD]Scale Lookup 2[/TD]
[TD][/TD]
[TD][/TD]
[TD]Scale[/TD]
[TD]Material[/TD]
[/TR]
[TR]
[TD="align: right"]6/11/2018[/TD]
[TD="align: right"]67274006[/TD]
[TD="align: right"]1235[/TD]
[TD="align: right"]423[/TD]
[TD="align: right"]300[/TD]
[TD="align: center"]#REF![/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]100[/TD]
[TD]67458258[/TD]
[/TR]
[TR]
[TD="align: right"]6/8/2018[/TD]
[TD="align: right"]67291470[/TD]
[TD="align: right"]1236[/TD]
[TD="align: right"]7500[/TD]
[TD="align: right"]5000[/TD]
[TD="align: center"]#REF![/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]150[/TD]
[TD]67458258[/TD]
[/TR]
[TR]
[TD="align: right"]6/6/2018[/TD]
[TD="align: right"]84170740[/TD]
[TD="align: right"]1270[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]500[/TD]
[TD="align: center"]#N/A[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]200[/TD]
[TD]67458258[/TD]
[/TR]
[TR]
[TD="align: right"]5/26/2018[/TD]
[TD="align: right"]67274072[/TD]
[TD="align: right"]1271[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]1000[/TD]
[TD="align: center"]#REF![/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]250[/TD]
[TD]67458258[/TD]
[/TR]
[TR]
[TD="align: right"]5/22/2018[/TD]
[TD="align: right"]67274072[/TD]
[TD="align: right"]1289[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]1000[/TD]
[TD="align: center"]#REF![/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]300[/TD]
[TD]67458258[/TD]
[/TR]
[TR]
[TD="align: right"]5/22/2018[/TD]
[TD="align: right"]67376093[/TD]
[TD="align: right"]1312[/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"]1000[/TD]
[TD="align: center"]#N/A[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]500[/TD]
[TD]67458258[/TD]
[/TR]
[TR]
[TD="align: right"]5/17/2018[/TD]
[TD="align: right"]84170957[/TD]
[TD="align: right"]1314[/TD]
[TD="align: right"]600[/TD]
[TD="align: right"]500[/TD]
[TD="align: center"]#REF![/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1000[/TD]
[TD]67458258[/TD]
[/TR]
[TR]
[TD="align: right"]5/16/2018[/TD]
[TD="align: right"]67363858[/TD]
[TD="align: right"]1317[/TD]
[TD="align: right"]800[/TD]
[TD="align: right"]500[/TD]
[TD="align: center"]#N/A[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1500[/TD]
[TD]67458258[/TD]
[/TR]
[TR]
[TD="align: right"]5/15/2018[/TD]
[TD="align: right"]67458258[/TD]
[TD="align: right"]1345[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]500[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]100[/TD]
[TD]67274006[/TD]
[/TR]
[TR]
[TD="align: right"]5/9/2018[/TD]
[TD="align: right"]67274072[/TD]
[TD="align: right"]1367[/TD]
[TD="align: right"]364[/TD]
[TD="align: right"]300[/TD]
[TD="align: center"]#REF![/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]150[/TD]
[TD]67274006[/TD]
[/TR]
[TR]
[TD="align: right"]5/9/2018[/TD]
[TD="align: right"]67274072[/TD]
[TD="align: right"]1368[/TD]
[TD="align: right"]364[/TD]
[TD="align: right"]300[/TD]
[TD="align: center"]#REF![/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]200[/TD]
[TD]67274006[/TD]
[/TR]
[TR]
[TD="align: right"]4/27/2018[/TD]
[TD="align: right"]67458258[/TD]
[TD="align: right"]1417[/TD]
[TD="align: right"]1200[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]250[/TD]
[TD]67274006[/TD]
[/TR]
[TR]
[TD="align: right"]4/27/2018[/TD]
[TD="align: right"]84170740[/TD]
[TD="align: right"]1418[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]500[/TD]
[TD="align: center"]#N/A[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]300[/TD]
[TD]67274006[/TD]
[/TR]
[TR]
[TD="align: right"]4/27/2018[/TD]
[TD="align: right"]84170957[/TD]
[TD="align: right"]1419[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]200[/TD]
[TD="align: center"]#REF![/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]500[/TD]
[TD]67274006[/TD]
[/TR]
[TR]
[TD="align: right"]4/21/2018[/TD]
[TD="align: right"]67274072[/TD]
[TD="align: right"]1422[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]500[/TD]
[TD="align: center"]#REF![/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1000[/TD]
[TD]67274006[/TD]
[/TR]
[TR]
[TD="align: right"]4/12/2018[/TD]
[TD="align: right"]84170957[/TD]
[TD="align: right"]1470[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]500[/TD]
[TD="align: center"]#REF![/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]100[/TD]
[TD]67274072[/TD]
[/TR]
[TR]
[TD="align: right"]4/12/2018[/TD]
[TD="align: right"]84170957[/TD]
[TD="align: right"]1471[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]500[/TD]
[TD="align: center"]#REF![/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]150[/TD]
[TD]67274072[/TD]
[/TR]
[TR]
[TD="align: right"]4/10/2018[/TD]
[TD="align: right"]67274006[/TD]
[TD="align: right"]1472[/TD]
[TD="align: right"]423[/TD]
[TD="align: right"]300[/TD]
[TD="align: center"]#REF![/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]200[/TD]
[TD]67274072[/TD]
[/TR]
[TR]
[TD="align: right"]4/4/2018[/TD]
[TD="align: right"]67274072[/TD]
[TD="align: right"]1477[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]500[/TD]
[TD="align: center"]#REF![/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]250[/TD]
[TD]67274072[/TD]
[/TR]
[TR]
[TD="align: right"]4/4/2018[/TD]
[TD="align: right"]67291470[/TD]
[TD="align: right"]1489[/TD]
[TD="align: right"]10720[/TD]
[TD="align: right"]10000[/TD]
[TD="align: center"]#REF![/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]300[/TD]
[TD]67274072[/TD]
[/TR]
[TR]
[TD="align: right"]4/4/2018[/TD]
[TD="align: right"]84170715[/TD]
[TD="align: right"]1490[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]200[/TD]
[TD="align: center"]#N/A[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]500[/TD]
[TD]67274072[/TD]
[/TR]
[TR]
[TD="align: right"]4/4/2018[/TD]
[TD="align: right"]84170715[/TD]
[TD="align: right"]1491[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]200[/TD]
[TD="align: center"]#N/A[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1000[/TD]
[TD]67274072[/TD]
[/TR]
[TR]
[TD="align: right"]3/28/2018[/TD]
[TD="align: right"]67274006[/TD]
[TD="align: right"]1525[/TD]
[TD="align: right"]423[/TD]
[TD="align: right"]300[/TD]
[TD="align: center"]#REF![/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1500[/TD]
[TD]67274072[/TD]
[/TR]
[TR]
[TD="align: right"]3/23/2018[/TD]
[TD="align: right"]67458258[/TD]
[TD="align: right"]1528[/TD]
[TD="align: right"]600[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]500[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1000[/TD]
[TD]67291470[/TD]
[/TR]
[TR]
[TD="align: right"]3/22/2018[/TD]
[TD="align: right"]67363858[/TD]
[TD="align: right"]1535[/TD]
[TD="align: right"]790[/TD]
[TD="align: right"]500[/TD]
[TD="align: center"]#N/A[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]5000[/TD]
[TD]67291470[/TD]
[/TR]
[TR]
[TD="align: right"]3/19/2018[/TD]
[TD="align: right"]67274072[/TD]
[TD="align: right"]1582[/TD]
[TD="align: right"]139[/TD]
[TD="align: right"]100[/TD]
[TD="align: center"]#REF![/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]10000[/TD]
[TD]67291470[/TD]
[/TR]
[TR]
[TD="align: right"]3/19/2018[/TD]
[TD="align: right"]67274072[/TD]
[TD="align: right"]1583[/TD]
[TD="align: right"]395[/TD]
[TD="align: right"]300[/TD]
[TD="align: center"]#REF![/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]15000[/TD]
[TD]67291470[/TD]
[/TR]
[TR]
[TD="align: right"]3/19/2018[/TD]
[TD="align: right"]67274072[/TD]
[TD="align: right"]1584[/TD]
[TD="align: right"]395[/TD]
[TD="align: right"]300[/TD]
[TD="align: center"]#REF![/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]20000[/TD]
[TD]67291470[/TD]
[/TR]
[TR]
[TD="align: right"]3/15/2018[/TD]
[TD="align: right"]84170957[/TD]
[TD="align: right"]1592[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]300[/TD]
[TD="align: center"]#REF![/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]30000[/TD]
[TD]67291470[/TD]
[/TR]
[TR]
[TD="align: right"]3/15/2018[/TD]
[TD="align: right"]84170957[/TD]
[TD="align: right"]1593[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]300[/TD]
[TD="align: center"]#REF![/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]100[/TD]
[TD]84170957[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]150[/TD]
[TD]84170957[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]200[/TD]
[TD]84170957[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]250[/TD]
[TD]84170957[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]300[/TD]
[TD]84170957[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]500[/TD]
[TD]84170957[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1000[/TD]
[TD]84170957[/TD]
[/TR]
</tbody>[/TABLE]


My scales can be seen here in matrix form, but I have them in columns side by side for my vertical lookup

[TABLE="width: 640"]
<tbody>[TR]
[TD="class: xl66, width: 64"]SKU[/TD]
[TD="class: xl67, width: 64"]Scale 1[/TD]
[TD="class: xl67, width: 64"]Scale 2[/TD]
[TD="class: xl67, width: 64"]Scale 3[/TD]
[TD="class: xl67, width: 64"]Scale 4[/TD]
[TD="class: xl67, width: 64"]Scale 5[/TD]
[TD="class: xl67, width: 64"]Scale 6[/TD]
[TD="class: xl67, width: 64"]Scale 7[/TD]
[TD="class: xl67, width: 64"]Scale 8[/TD]
[TD="class: xl67, width: 64"]Scale 9[/TD]
[/TR]
[TR]
[TD="class: xl68"]67458258[/TD]
[TD="class: xl69"]100[/TD]
[TD="class: xl69"]100[/TD]
[TD="class: xl69"]150[/TD]
[TD="class: xl69"]200[/TD]
[TD="class: xl69"]250[/TD]
[TD="class: xl69"]300[/TD]
[TD="class: xl69"]500[/TD]
[TD="class: xl69"]1000[/TD]
[TD="class: xl69"]1500[/TD]
[/TR]
[TR]
[TD="class: xl68"]67274006[/TD]
[TD="class: xl69"]100[/TD]
[TD="class: xl69"]100[/TD]
[TD="class: xl69"]150[/TD]
[TD="class: xl69"]200[/TD]
[TD="class: xl69"]250[/TD]
[TD="class: xl69"]300[/TD]
[TD="class: xl69"]500[/TD]
[TD="class: xl69"]1000[/TD]
[TD="class: xl70"][/TD]
[/TR]
[TR]
[TD="class: xl68"]67274072[/TD]
[TD="class: xl69"]100[/TD]
[TD="class: xl69"]100[/TD]
[TD="class: xl69"]150[/TD]
[TD="class: xl69"]200[/TD]
[TD="class: xl69"]250[/TD]
[TD="class: xl69"]300[/TD]
[TD="class: xl69"]500[/TD]
[TD="class: xl69"]1000[/TD]
[TD="class: xl69"]1500[/TD]
[/TR]
[TR]
[TD="class: xl68"]67291470[/TD]
[TD="class: xl69"]1000[/TD]
[TD="class: xl69"]1000[/TD]
[TD="class: xl69"]5000[/TD]
[TD="class: xl69"]10000[/TD]
[TD="class: xl69"]15000[/TD]
[TD="class: xl69"]20000[/TD]
[TD="class: xl69"]30000[/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[/TR]
[TR]
[TD="class: xl68"]67363858[/TD]
[TD="class: xl69"]100[/TD]
[TD="class: xl69"]100[/TD]
[TD="class: xl69"]150[/TD]
[TD="class: xl69"]200[/TD]
[TD="class: xl69"]250[/TD]
[TD="class: xl69"]300[/TD]
[TD="class: xl69"]500[/TD]
[TD="class: xl69"]1000[/TD]
[TD="class: xl69"]1500[/TD]
[/TR]
[TR]
[TD="class: xl68"]67376093[/TD]
[TD="class: xl69"]100[/TD]
[TD="class: xl69"]100[/TD]
[TD="class: xl69"]150[/TD]
[TD="class: xl69"]200[/TD]
[TD="class: xl69"]250[/TD]
[TD="class: xl69"]300[/TD]
[TD="class: xl69"]500[/TD]
[TD="class: xl69"]1000[/TD]
[TD="class: xl69"]1500[/TD]
[/TR]
[TR]
[TD="class: xl68"]84170372[/TD]
[TD="class: xl69"]100[/TD]
[TD="class: xl69"]100[/TD]
[TD="class: xl69"]150[/TD]
[TD="class: xl69"]200[/TD]
[TD="class: xl69"]250[/TD]
[TD="class: xl69"]300[/TD]
[TD="class: xl69"]500[/TD]
[TD="class: xl69"]1000[/TD]
[TD="class: xl70"][/TD]
[/TR]
[TR]
[TD="class: xl68"]84170715[/TD]
[TD="class: xl69"]100[/TD]
[TD="class: xl69"]100[/TD]
[TD="class: xl69"]150[/TD]
[TD="class: xl69"]200[/TD]
[TD="class: xl69"]250[/TD]
[TD="class: xl69"]300[/TD]
[TD="class: xl69"]500[/TD]
[TD="class: xl69"]1000[/TD]
[TD="class: xl70"][/TD]
[/TR]
[TR]
[TD="class: xl68"]84170733[/TD]
[TD="class: xl69"]500[/TD]
[TD="class: xl69"]500[/TD]
[TD="class: xl69"]1000[/TD]
[TD="class: xl69"]2000[/TD]
[TD="class: xl69"]3000[/TD]
[TD="class: xl69"]5000[/TD]
[TD="class: xl69"]7000[/TD]
[TD="class: xl69"]9000[/TD]
[TD="class: xl70"][/TD]
[/TR]
[TR]
[TD="class: xl68"]84170740[/TD]
[TD="class: xl69"]500[/TD]
[TD="class: xl69"]500[/TD]
[TD="class: xl69"]1000[/TD]
[TD="class: xl69"]2000[/TD]
[TD="class: xl69"]3000[/TD]
[TD="class: xl69"]5000[/TD]
[TD="class: xl69"]7000[/TD]
[TD="class: xl69"]9000[/TD]
[TD="class: xl70"][/TD]
[/TR]
[TR]
[TD="class: xl68"]84170781[/TD]
[TD="class: xl69"]100[/TD]
[TD="class: xl69"]100[/TD]
[TD="class: xl69"]150[/TD]
[TD="class: xl69"]200[/TD]
[TD="class: xl69"]250[/TD]
[TD="class: xl69"]300[/TD]
[TD="class: xl69"]500[/TD]
[TD="class: xl69"]1000[/TD]
[TD="class: xl70"][/TD]
[/TR]
[TR]
[TD="class: xl68"]84170828[/TD]
[TD="class: xl69"]100[/TD]
[TD="class: xl69"]100[/TD]
[TD="class: xl69"]250[/TD]
[TD="class: xl69"]500[/TD]
[TD="class: xl69"]1000[/TD]
[TD="class: xl69"]2000[/TD]
[TD="class: xl69"]6000[/TD]
[TD="class: xl69"]9000[/TD]
[TD="class: xl70"][/TD]
[/TR]
[TR]
[TD="class: xl68"]84170957[/TD]
[TD="class: xl69"]100[/TD]
[TD="class: xl69"]100[/TD]
[TD="class: xl69"]150[/TD]
[TD="class: xl69"]200[/TD]
[TD="class: xl69"]250[/TD]
[TD="class: xl69"]300[/TD]
[TD="class: xl69"]500[/TD]
[TD="class: xl69"]1000[/TD]
[TD="class: xl70"][/TD]
[/TR]
[TR]
[TD="class: xl68"]84171143[/TD]
[TD="class: xl69"]100[/TD]
[TD="class: xl69"]100[/TD]
[TD="class: xl69"]250[/TD]
[TD="class: xl69"]500[/TD]
[TD="class: xl69"]1000[/TD]
[TD="class: xl69"]2000[/TD]
[TD="class: xl69"]6000[/TD]
[TD="class: xl69"]9000[/TD]
[TD="class: xl70"][/TD]
[/TR]
</tbody>[/TABLE]

 

[Intermedius]

Anyone have an idea about how to approach this? I am not sure how to create my table into spreadsheet on this forum so i have added column names.

Is this possible?

Scale Lookup 1 =INDEX($G$2:$G$40,MATCH(D2,$G$2:$G$40,1))

Scale Lookup 2 = =INDEX($G$2:$G$40,MATCH(D2,$G$2:$G$40,1),MATCH(B2,$H$2:$H$40,0))


[TABLE="class: cms_table, width: 831"]
<tbody>[TR]
[TD]Column A[/TD]
[TD]Column B[/TD]
[TD]Column C[/TD]
[TD]Column D[/TD]
[TD]Column E[/TD]
[TD]Column F[/TD]
[TD]Column G[/TD]
[TD]Column H[/TD]
[/TR]
[TR]
[TD]Pstng Date[/TD]
[TD]Material[/TD]
[TD]Purchase Order[/TD]
[TD]PO Quantity[/TD]
[TD]Scale Lookup 1[/TD]
[TD]Scale Lookup 2[/TD]
[TD]Scale[/TD]
[TD]Material[/TD]
[/TR]
[TR]
[TD="align: right"]6/11/2018[/TD]
[TD="align: right"]67274006[/TD]
[TD="align: right"]1235[/TD]
[TD="align: right"]423[/TD]
[TD="align: right"]300[/TD]
[TD="align: center"]#REF![/TD]
[TD="align: right"]100[/TD]
[TD]67458258[/TD]
[/TR]
[TR]
[TD="align: right"]6/8/2018[/TD]
[TD="align: right"]67291470[/TD]
[TD="align: right"]1236[/TD]
[TD="align: right"]7500[/TD]
[TD="align: right"]5000[/TD]
[TD="align: center"]#REF![/TD]
[TD="align: right"]150[/TD]
[TD]67458258[/TD]
[/TR]
[TR]
[TD="align: right"]6/6/2018[/TD]
[TD="align: right"]84170740[/TD]
[TD="align: right"]1270[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]500[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: right"]200[/TD]
[TD]67458258[/TD]
[/TR]
[TR]
[TD="align: right"]5/26/2018[/TD]
[TD="align: right"]67274072[/TD]
[TD="align: right"]1271[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]1000[/TD]
[TD="align: center"]#REF![/TD]
[TD="align: right"]250[/TD]
[TD]67458258[/TD]
[/TR]
[TR]
[TD="align: right"]5/22/2018[/TD]
[TD="align: right"]67274072[/TD]
[TD="align: right"]1289[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]1000[/TD]
[TD="align: center"]#REF![/TD]
[TD="align: right"]300[/TD]
[TD]67458258[/TD]
[/TR]
[TR]
[TD="align: right"]5/22/2018[/TD]
[TD="align: right"]67376093[/TD]
[TD="align: right"]1312[/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"]1000[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: right"]500[/TD]
[TD]67458258[/TD]
[/TR]
[TR]
[TD="align: right"]5/17/2018[/TD]
[TD="align: right"]84170957[/TD]
[TD="align: right"]1314[/TD]
[TD="align: right"]600[/TD]
[TD="align: right"]500[/TD]
[TD="align: center"]#REF![/TD]
[TD="align: right"]1000[/TD]
[TD]67458258[/TD]
[/TR]
[TR]
[TD="align: right"]5/16/2018[/TD]
[TD="align: right"]67363858[/TD]
[TD="align: right"]1317[/TD]
[TD="align: right"]800[/TD]
[TD="align: right"]500[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: right"]1500[/TD]
[TD]67458258[/TD]
[/TR]
[TR]
[TD="align: right"]5/15/2018[/TD]
[TD="align: right"]67458258[/TD]
[TD="align: right"]1345[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]100[/TD]
[TD]67274006[/TD]
[/TR]
[TR]
[TD="align: right"]5/9/2018[/TD]
[TD="align: right"]67274072[/TD]
[TD="align: right"]1367[/TD]
[TD="align: right"]364[/TD]
[TD="align: right"]300[/TD]
[TD="align: center"]#REF![/TD]
[TD="align: right"]150[/TD]
[TD]67274006[/TD]
[/TR]
[TR]
[TD="align: right"]5/9/2018[/TD]
[TD="align: right"]67274072[/TD]
[TD="align: right"]1368[/TD]
[TD="align: right"]364[/TD]
[TD="align: right"]300[/TD]
[TD="align: center"]#REF![/TD]
[TD="align: right"]200[/TD]
[TD]67274006[/TD]
[/TR]
[TR]
[TD="align: right"]4/27/2018[/TD]
[TD="align: right"]67458258[/TD]
[TD="align: right"]1417[/TD]
[TD="align: right"]1200[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]250[/TD]
[TD]67274006[/TD]
[/TR]
[TR]
[TD="align: right"]4/27/2018[/TD]
[TD="align: right"]84170740[/TD]
[TD="align: right"]1418[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]500[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: right"]300[/TD]
[TD]67274006[/TD]
[/TR]
[TR]
[TD="align: right"]4/27/2018[/TD]
[TD="align: right"]84170957[/TD]
[TD="align: right"]1419[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]200[/TD]
[TD="align: center"]#REF![/TD]
[TD="align: right"]500[/TD]
[TD]67274006[/TD]
[/TR]
[TR]
[TD="align: right"]4/21/2018[/TD]
[TD="align: right"]67274072[/TD]
[TD="align: right"]1422[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]500[/TD]
[TD="align: center"]#REF![/TD]
[TD="align: right"]1000[/TD]
[TD]67274006[/TD]
[/TR]
[TR]
[TD="align: right"]4/12/2018[/TD]
[TD="align: right"]84170957[/TD]
[TD="align: right"]1470[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]500[/TD]
[TD="align: center"]#REF![/TD]
[TD="align: right"]100[/TD]
[TD]67274072[/TD]
[/TR]
[TR]
[TD="align: right"]4/12/2018[/TD]
[TD="align: right"]84170957[/TD]
[TD="align: right"]1471[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]500[/TD]
[TD="align: center"]#REF![/TD]
[TD="align: right"]150[/TD]
[TD]67274072[/TD]
[/TR]
[TR]
[TD="align: right"]4/10/2018[/TD]
[TD="align: right"]67274006[/TD]
[TD="align: right"]1472[/TD]
[TD="align: right"]423[/TD]
[TD="align: right"]300[/TD]
[TD="align: center"]#REF![/TD]
[TD="align: right"]200[/TD]
[TD]67274072[/TD]
[/TR]
[TR]
[TD="align: right"]4/4/2018[/TD]
[TD="align: right"]67274072[/TD]
[TD="align: right"]1477[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]500[/TD]
[TD="align: center"]#REF![/TD]
[TD="align: right"]250[/TD]
[TD]67274072[/TD]
[/TR]
[TR]
[TD="align: right"]4/4/2018[/TD]
[TD="align: right"]67291470[/TD]
[TD="align: right"]1489[/TD]
[TD="align: right"]10720[/TD]
[TD="align: right"]10000[/TD]
[TD="align: center"]#REF![/TD]
[TD="align: right"]300[/TD]
[TD]67274072[/TD]
[/TR]
[TR]
[TD="align: right"]4/4/2018[/TD]
[TD="align: right"]84170715[/TD]
[TD="align: right"]1490[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]200[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: right"]500[/TD]
[TD]67274072[/TD]
[/TR]
[TR]
[TD="align: right"]4/4/2018[/TD]
[TD="align: right"]84170715[/TD]
[TD="align: right"]1491[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]200[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: right"]1000[/TD]
[TD]67274072[/TD]
[/TR]
[TR]
[TD="align: right"]3/28/2018[/TD]
[TD="align: right"]67274006[/TD]
[TD="align: right"]1525[/TD]
[TD="align: right"]423[/TD]
[TD="align: right"]300[/TD]
[TD="align: center"]#REF![/TD]
[TD="align: right"]1500[/TD]
[TD]67274072[/TD]
[/TR]
[TR]
[TD="align: right"]3/23/2018[/TD]
[TD="align: right"]67458258[/TD]
[TD="align: right"]1528[/TD]
[TD="align: right"]600[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]1000[/TD]
[TD]67291470[/TD]
[/TR]
[TR]
[TD="align: right"]3/22/2018[/TD]
[TD="align: right"]67363858[/TD]
[TD="align: right"]1535[/TD]
[TD="align: right"]790[/TD]
[TD="align: right"]500[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: right"]5000[/TD]
[TD]67291470[/TD]
[/TR]
[TR]
[TD="align: right"]3/19/2018[/TD]
[TD="align: right"]67274072[/TD]
[TD="align: right"]1582[/TD]
[TD="align: right"]139[/TD]
[TD="align: right"]100[/TD]
[TD="align: center"]#REF![/TD]
[TD="align: right"]10000[/TD]
[TD]67291470[/TD]
[/TR]
[TR]
[TD="align: right"]3/19/2018[/TD]
[TD="align: right"]67274072[/TD]
[TD="align: right"]1583[/TD]
[TD="align: right"]395[/TD]
[TD="align: right"]300[/TD]
[TD="align: center"]#REF![/TD]
[TD="align: right"]15000[/TD]
[TD]67291470[/TD]
[/TR]
[TR]
[TD="align: right"]3/19/2018[/TD]
[TD="align: right"]67274072[/TD]
[TD="align: right"]1584[/TD]
[TD="align: right"]395[/TD]
[TD="align: right"]300[/TD]
[TD="align: center"]#REF![/TD]
[TD="align: right"]20000[/TD]
[TD]67291470[/TD]
[/TR]
[TR]
[TD="align: right"]3/15/2018[/TD]
[TD="align: right"]84170957[/TD]
[TD="align: right"]1592[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]300[/TD]
[TD="align: center"]#REF![/TD]
[TD="align: right"]30000[/TD]
[TD]67291470[/TD]
[/TR]
[TR]
[TD="align: right"]3/15/2018[/TD]
[TD="align: right"]84170957[/TD]
[TD="align: right"]1593[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]300[/TD]
[TD="align: center"]#REF![/TD]
[TD="align: right"]100[/TD]
[TD]84170957[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]150[/TD]
[TD]84170957[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]200[/TD]
[TD]84170957[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]250[/TD]
[TD]84170957[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]300[/TD]
[TD]84170957[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]500[/TD]
[TD]84170957[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1000[/TD]
[TD]84170957[/TD]
[/TR]
</tbody>[/TABLE]

 

[Intermedius]

Anyone confident enough to say it can't be done?

 

[Aladin Akyurek]

Try to post the expected results instead of the formula results. State clearly in words how the expected results obtain from the matrix you posted.

 

[Marcelo Branco]

Not sure i understand what you are looking for,
See if this does what you need

Array formula in F2 copied down
=IFERROR(INDEX(G$2:G$40,MATCH(D2,IF(H$2:H$40=B2,G$2:G$40))),"Not found")
confirmed with Ctrl+Shift+Enter, not just Enter

M.

 

[Intermedius]

Try to post the expected results instead of the formula results. State clearly in words how the expected results obtain from the matrix you posted.

Hello Aladin Akyurek,

I think the problem is that i do not know how to present excel data ineligibly for others on Vbulletin.

I have altered my table below.

In normal conditions, columns F & G would be on a different sheet. The result i desire to achieve is to assign the appropriate pricing scale (found in column F) to the quantity in column D. However, the pricing scale must match the Material. So in essence, Column A must match Column G (hand typed), so as to allow column C to approximate the corresponding value of column F.

The desired result would be in be what i have now typed in Column E.

Column D has a standard index match value using "1" as the "match type".

If a PO quantity hits above a scale for that material, it needs to assigned to it the largest scale beneath that figure quantity.

I guess this is what Pearson would call "bracket pricing": http://www.cpearson.com/excel/pricing.htm


[TABLE="class: grid, width: 567, align: center"]
<tbody>[TR]
[TD]Column A[/TD]
[TD]Column B[/TD]
[TD]Column C[/TD]
[TD]Column D[/TD]
[TD]Column E[/TD]
[TD]Column F[/TD]
[TD]Column G[/TD]
[TD]Do Columns E & F Match?[/TD]
[/TR]
[TR]
[TD]Material[/TD]
[TD]Purchase Order[/TD]
[TD]PO Quantity[/TD]
[TD]Scale Lookup 1[/TD]
[TD]Scale Lookup 2[/TD]
[TD]Scale[/TD]
[TD]Material[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]67274006[/TD]
[TD]1235[/TD]
[TD]750[/TD]
[TD]750[/TD]
[TD]700[/TD]
[TD]100[/TD]
[TD]67458258[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]67458258[/TD]
[TD]1236[/TD]
[TD]220[/TD]
[TD]200[/TD]
[TD]200[/TD]
[TD]150[/TD]
[TD]67458258[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]67274072[/TD]
[TD]1270[/TD]
[TD]220[/TD]
[TD]200[/TD]
[TD]175[/TD]
[TD]200[/TD]
[TD]67458258[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]67274006[/TD]
[TD]1271[/TD]
[TD]1000[/TD]
[TD]750[/TD]
[TD]900[/TD]
[TD]250[/TD]
[TD]67458258[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]67274072[/TD]
[TD]1289[/TD]
[TD]1000[/TD]
[TD]750[/TD]
[TD]750[/TD]
[TD]300[/TD]
[TD]67458258[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]67274006[/TD]
[TD]1312[/TD]
[TD]2000[/TD]
[TD]750[/TD]
[TD]1300[/TD]
[TD]500[/TD]
[TD]67458258[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]67458258[/TD]
[TD]1314[/TD]
[TD]600[/TD]
[TD]500[/TD]
[TD]500[/TD]
[TD]1000[/TD]
[TD]67458258[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]67274072[/TD]
[TD]1317[/TD]
[TD]800[/TD]
[TD]750[/TD]
[TD]750[/TD]
[TD]1500[/TD]
[TD]67458258[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]67274006[/TD]
[TD]1345[/TD]
[TD]500[/TD]
[TD]500[/TD]
[TD]500[/TD]
[TD]100[/TD]
[TD]67274006[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]67274072[/TD]
[TD]1367[/TD]
[TD]364[/TD]
[TD]300[/TD]
[TD]250[/TD]
[TD]300[/TD]
[TD]67274006[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]67274006[/TD]
[TD]1368[/TD]
[TD]364[/TD]
[TD]300[/TD]
[TD]300[/TD]
[TD]500[/TD]
[TD]67274006[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]67458258[/TD]
[TD]1417[/TD]
[TD]1200[/TD]
[TD]750[/TD]
[TD]1000[/TD]
[TD]700[/TD]
[TD]67274006[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]67274072[/TD]
[TD]1418[/TD]
[TD]500[/TD]
[TD]500[/TD]
[TD]500[/TD]
[TD]900[/TD]
[TD]67274006[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]67274006[/TD]
[TD]1419[/TD]
[TD]200[/TD]
[TD]200[/TD]
[TD]100[/TD]
[TD]1100[/TD]
[TD]67274006[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]67274072[/TD]
[TD]1422[/TD]
[TD]500[/TD]
[TD]500[/TD]
[TD]500[/TD]
[TD]1300[/TD]
[TD]67274006[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]67274006[/TD]
[TD]1470[/TD]
[TD]500[/TD]
[TD]500[/TD]
[TD]500[/TD]
[TD]100[/TD]
[TD]67274072[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]67458258[/TD]
[TD]1471[/TD]
[TD]500[/TD]
[TD]500[/TD]
[TD]500[/TD]
[TD]25[/TD]
[TD]67274072[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]67274072[/TD]
[TD]1472[/TD]
[TD]423[/TD]
[TD]300[/TD]
[TD]250[/TD]
[TD]175[/TD]
[TD]67274072[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]67274006[/TD]
[TD]1477[/TD]
[TD]200[/TD]
[TD]200[/TD]
[TD]100[/TD]
[TD]250[/TD]
[TD]67274072[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]67274072[/TD]
[TD]1489[/TD]
[TD]30[/TD]
[TD]#N/A[/TD]
[TD]25[/TD]
[TD]500[/TD]
[TD]67274072[/TD]
[TD]#N/A[/TD]
[/TR]
[TR]
[TD]67274072[/TD]
[TD]1490[/TD]
[TD]160[/TD]
[TD]150[/TD]
[TD]175[/TD]
[TD]750[/TD]
[TD]67274072[/TD]
[TD]FALSE[/TD]
[/TR]
</tbody>[/TABLE]

 

[Intermedius]

Not sure i understand what you are looking for,
See if this does what you need

Array formula in F2 copied down
=IFERROR(INDEX(G$2:G$40,MATCH(D2,IF(H$2:H$40=B2,G$2:G$40))),"Not found")
confirmed with Ctrl+Shift+Enter, not just Enter

M.

Hello Marcelo,

I see that your post shows after mine, but due to my composing while you posted you would not have seen My 3rd post.

I tried your array formula on the table in the 1st post, and i ran into similar issues as my index, match, match formula.

I tried your array formula on my latest table that has less junk in it.

I like your approach. Reading it, i thought the logic would do the trick, but it did not end up matching column E.

=IFERROR(INDEX($F$3:$F$23,MATCH(C3,IF($G$3:$G$23=A2,$F$3:$F$23))),"Not found")

[TABLE="class: grid, width: 567, align: left"]
<tbody>[TR]
[TD]Column A[/TD]
[TD]Column B[/TD]
[TD]Column C[/TD]
[TD]Column D[/TD]
[TD]Column E[/TD]
[TD]Column F[/TD]
[TD]Column G[/TD]
[TD]Do Columns D & E Match?[/TD]
[/TR]
[TR]
[TD]Material[/TD]
[TD]Purchase Order[/TD]
[TD]PO Quantity[/TD]
[TD]Scale Lookup 1[/TD]
[TD]Hand coded[/TD]
[TD]Scale[/TD]
[TD]Material[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]67274006[/TD]
[TD]1235[/TD]
[TD]750[/TD]
[TD]Not found[/TD]
[TD]700[/TD]
[TD]100[/TD]
[TD]67458258[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]67458258[/TD]
[TD]1236[/TD]
[TD]220[/TD]
[TD]100[/TD]
[TD]200[/TD]
[TD]150[/TD]
[TD]67458258[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]67274072[/TD]
[TD]1270[/TD]
[TD]220[/TD]
[TD]200[/TD]
[TD]175[/TD]
[TD]200[/TD]
[TD]67458258[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]67274006[/TD]
[TD]1271[/TD]
[TD]1000[/TD]
[TD]750[/TD]
[TD]900[/TD]
[TD]250[/TD]
[TD]67458258[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]67274072[/TD]
[TD]1289[/TD]
[TD]1000[/TD]
[TD]900[/TD]
[TD]750[/TD]
[TD]300[/TD]
[TD]67458258[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]67274006[/TD]
[TD]1312[/TD]
[TD]2000[/TD]
[TD]750[/TD]
[TD]1300[/TD]
[TD]500[/TD]
[TD]67458258[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]67458258[/TD]
[TD]1314[/TD]
[TD]600[/TD]
[TD]500[/TD]
[TD]500[/TD]
[TD]1000[/TD]
[TD]67458258[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]67274072[/TD]
[TD]1317[/TD]
[TD]800[/TD]
[TD]500[/TD]
[TD]750[/TD]
[TD]1500[/TD]
[TD]67458258[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]67274006[/TD]
[TD]1345[/TD]
[TD]500[/TD]
[TD]500[/TD]
[TD]500[/TD]
[TD]100[/TD]
[TD]67274006[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]67274072[/TD]
[TD]1367[/TD]
[TD]364[/TD]
[TD]300[/TD]
[TD]250[/TD]
[TD]300[/TD]
[TD]67274006[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]67274006[/TD]
[TD]1368[/TD]
[TD]364[/TD]
[TD]250[/TD]
[TD]300[/TD]
[TD]500[/TD]
[TD]67274006[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]67458258[/TD]
[TD]1417[/TD]
[TD]1200[/TD]
[TD]1100[/TD]
[TD]1000[/TD]
[TD]700[/TD]
[TD]67274006[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]67274072[/TD]
[TD]1418[/TD]
[TD]500[/TD]
[TD]500[/TD]
[TD]500[/TD]
[TD]900[/TD]
[TD]67274006[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]67274006[/TD]
[TD]1419[/TD]
[TD]200[/TD]
[TD]175[/TD]
[TD]100[/TD]
[TD]1100[/TD]
[TD]67274006[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]67274072[/TD]
[TD]1422[/TD]
[TD]500[/TD]
[TD]500[/TD]
[TD]500[/TD]
[TD]1300[/TD]
[TD]67274006[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]67274006[/TD]
[TD]1470[/TD]
[TD]500[/TD]
[TD]500[/TD]
[TD]500[/TD]
[TD]100[/TD]
[TD]67274072[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]67458258[/TD]
[TD]1471[/TD]
[TD]500[/TD]
[TD]500[/TD]
[TD]500[/TD]
[TD]25[/TD]
[TD]67274072[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]67274072[/TD]
[TD]1472[/TD]
[TD]423[/TD]
[TD]300[/TD]
[TD]250[/TD]
[TD]175[/TD]
[TD]67274072[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]67274006[/TD]
[TD]1477[/TD]
[TD]200[/TD]
[TD]175[/TD]
[TD]100[/TD]
[TD]250[/TD]
[TD]67274072[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]67274072[/TD]
[TD]1489[/TD]
[TD]30[/TD]
[TD]Not found[/TD]
[TD]25[/TD]
[TD]500[/TD]
[TD]67274072[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]67274072[/TD]
[TD]1490[/TD]
[TD]160[/TD]
[TD]25[/TD]
[TD]175[/TD]
[TD]750[/TD]
[TD]67274072[/TD]
[TD]FALSE[/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

In E2 control+shift+enter, not just enter, and copy down:

=VLOOKUP($C2,IF($G$2:$G$22=$A2,$F$2:$F$22),1,1)

 

[Intermedius]

In E2 control+shift+enter, not just enter, and copy down:

=VLOOKUP($C2,IF($G$2:$G$22=$A2,$F$2:$F$22),1,1)

It worked on My 2nd table. Appreciate the help. I couldn't get my head around it, but i knew enough that my normal approach of an index,match, match, or a multi column index/match array were not logical for this purpose.

Thank you to Marcelo too. I think maybe i did something wrong because your logic looks identical to Aladin's to My eyes.