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

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

<tbody>
</tbody>

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

SKU	Scale 1	Scale 2	Scale 3	Scale 4	Scale 5	Scale 6	Scale 7	Scale 8	Scale 9
67458258	100	100	150	200	250	300	500	1000	1500
67274006	100	100	150	200	250	300	500	1000
67274072	100	100	150	200	250	300	500	1000	1500
67291470	1000	1000	5000	10000	15000	20000	30000
67363858	100	100	150	200	250	300	500	1000	1500
67376093	100	100	150	200	250	300	500	1000	1500
84170372	100	100	150	200	250	300	500	1000
84170715	100	100	150	200	250	300	500	1000
84170733	500	500	1000	2000	3000	5000	7000	9000
84170740	500	500	1000	2000	3000	5000	7000	9000
84170781	100	100	150	200	250	300	500	1000
84170828	100	100	250	500	1000	2000	6000	9000
84170957	100	100	150	200	250	300	500	1000
84171143	100	100	250	500	1000	2000	6000	9000

<tbody>
</tbody>

 

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

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

<tbody>
</tbody>

 

[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

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

<tbody>
</tbody>

 

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

Column A	Column B	Column C	Column D	Column E	Column F	Column G	Do Columns D & E Match?
Material	Purchase Order	PO Quantity	Scale Lookup 1	Hand coded	Scale	Material
67274006	1235	750	Not found	700	100	67458258	FALSE
67458258	1236	220	100	200	150	67458258	FALSE
67274072	1270	220	200	175	200	67458258	FALSE
67274006	1271	1000	750	900	250	67458258	FALSE
67274072	1289	1000	900	750	300	67458258	FALSE
67274006	1312	2000	750	1300	500	67458258	FALSE
67458258	1314	600	500	500	1000	67458258	TRUE
67274072	1317	800	500	750	1500	67458258	FALSE
67274006	1345	500	500	500	100	67274006	TRUE
67274072	1367	364	300	250	300	67274006	FALSE
67274006	1368	364	250	300	500	67274006	FALSE
67458258	1417	1200	1100	1000	700	67274006	FALSE
67274072	1418	500	500	500	900	67274006	TRUE
67274006	1419	200	175	100	1100	67274006	FALSE
67274072	1422	500	500	500	1300	67274006	TRUE
67274006	1470	500	500	500	100	67274072	TRUE
67458258	1471	500	500	500	25	67274072	TRUE
67274072	1472	423	300	250	175	67274072	FALSE
67274006	1477	200	175	100	250	67274072	FALSE
67274072	1489	30	Not found	25	500	67274072	FALSE
67274072	1490	160	25	175	750	67274072	FALSE

<tbody>
</tbody>

 

[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.