Look up using index/match not working with duplicate values

[grantjw]

[TABLE="width: 410"]
<colgroup><col span="4"><col><col></colgroup><tbody></tbody>[/TABLE]
[TABLE="width: 474"]
<colgroup><col span="5"><col><col></colgroup><tbody></tbody>[/TABLE]
[TABLE="width: 484"]
<colgroup><col span="2"><col><col span="2"><col><col></colgroup><tbody>[TR]
[TD]1/A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD="colspan: 2"]DATA TABLE Sheet1[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]LOOKUP TABLE Sheet 2[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Price[/TD]
[TD]Qty[/TD]
[TD][/TD]
[TD][/TD]
[TD]Desired Qty[/TD]
[TD]Price[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]1.08[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]50[/TD]
[TD]1.05[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]1.05[/TD]
[TD]50[/TD]
[TD][/TD]
[TD][/TD]
[TD]100[/TD]
[TD]1.02[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]1.02[/TD]
[TD]100[/TD]
[TD][/TD]
[TD][/TD]
[TD]100[/TD]
[TD]1.02[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]0.99[/TD]
[TD]100[/TD]
[TD][/TD]
[TD][/TD]
[TD]100[/TD]
[TD]1.02[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]0.96[/TD]
[TD]100[/TD]
[TD][/TD]
[TD][/TD]
[TD]75[/TD]
[TD]0.93[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]0.93[/TD]
[TD]75[/TD]
[TD][/TD]
[TD][/TD]
[TD]75[/TD]
[TD]0.93[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]0.9[/TD]
[TD]75[/TD]
[TD][/TD]
[TD][/TD]
[TD]50[/TD]
[TD]1.05[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]0.87[/TD]
[TD]50[/TD]
[TD][/TD]
[TD][/TD]
[TD]50[/TD]
[TD]1.05[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]0.84[/TD]
[TD]50[/TD]
[TD][/TD]
[TD][/TD]
[TD]25[/TD]
[TD]0.81[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]0.81[/TD]
[TD]25[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]0.78[/TD]
[TD]25[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]0.75[/TD]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]0.72[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]0.69[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]0.66[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]0.63[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD]0.6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD]0.57[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]23[/TD]
[TD]0.54[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]24[/TD]
[TD]0.51[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]25[/TD]
[TD]0.48[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]26[/TD]
[TD]0.45[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]27[/TD]
[TD]0.42[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]28[/TD]
[TD]0.39[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]29[/TD]
[TD]0.36[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]30[/TD]
[TD]0.33[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]31[/TD]
[TD]0.3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]32[/TD]
[TD]0.27[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]33[/TD]
[TD]0.24[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]34[/TD]
[TD]0.21[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]35[/TD]
[TD]0.18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]36[/TD]
[TD]0.15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]37[/TD]
[TD]0.12
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Data table on sheet 1
Data in column C can be changed and be placed or removed on any rows

Look up table, used by users on sheet 2
Desired Qty in col F
is used with the following index/match function =INDEX($B$5:$B$37,MATCH(F5,$C$5:$C$37,0)) in Col G
Duplicate values are not looked up properly.

Both tables are on same sheet for illustrative purposes.

Please Help!

Thanks for your time and effort

 

[Caribeiro77]

Maybe:

Book1
	A	B	C	D	E	F	G
1	Price	Qty				Desired Qty	Price
2	1.08					50	1.05
3	1.05	50				100	1.02
4	1.02	100				100	0.99
5	0.99	100				100	0.96
6	0.96	100				75	0.93
7	0.93	75				75	0.9
8	0.9	75				50	0.87
9	0.87	50				50	0.84
10	0.84	50				25	0.81
11	0.81	25
12	0.78	25
13	0.75	10
14	0.72
15	0.69
16	0.66
17	0.63
18	0.6
19	0.57
20	0.54
21	0.51
22	0.48
23	0.45
24	0.42
25	0.39
26	0.36
27	0.33
28	0.3
29	0.27
30	0.24
31	0.21
32	0.18
33	0.15
34	0.12
Sheet1

Cell Formulas
Range	Formula
G2	{=INDEX($A$2:$A$34,SMALL(IF(OR($B$2:$B$34=F2,$B$2:$B$34<>""),ROW($A$2:$A$34)-ROW($A$2)+2),ROW($F2:F2)-ROW($F$2)+1))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[grantjw]

Thanks for you reply. It is greatly appreciated. It works if the desired quantity stays the same. If the desired quantity changes and I drop the qty 50 from the data table and 50 from look up table, the qty/price pair should be 100 & 1.02. I think I need to change something in the row reference in your solution but an not sure what to change.

 

[Caribeiro77]

Can you post a visual example of what you just post? i.e. Drop qty50 on oth tables..

 

[grantjw]

I can't install the HTML plugin, my PC is locked down by our IT folks. I will do my best to provide clarity.

Column F, desired quantity, is linked to the Data table, Column B. Col. B can updated to add and remove values. First example started row 2 with 50 but can change. in the example below it starts at row 5 with 100. Col B can contain values from rows 2 to 34.

Col. F is linked to Col B. Using formulas in Col. F always display the first qty from Col. B in F2. So Col. F can grow or shrink depending on values in Col. B.

So, as Col. F values are updated (from Col. B) I need to display the the correct matching price in Col G (from Col A).

Your proposed solution did not look up the correct value when the desired quantity value in Col B moved its position. This is what It looked liked.

[TABLE="width: 474"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]Price[/TD]
[TD]Qty[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Desired Qty[/TD]
[TD]Price[/TD]
[/TR]
[TR]
[TD]1.08[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]100[/TD]
[TD]1.05[/TD]
[/TR]
[TR]
[TD]1.05[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]100[/TD]
[TD]1.02[/TD]
[/TR]
[TR]
[TD]1.02[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]75[/TD]
[TD]0.99[/TD]
[/TR]
[TR]
[TD]0.99[/TD]
[TD]100[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]75[/TD]
[TD]0.96[/TD]
[/TR]
[TR]
[TD]0.96[/TD]
[TD]100[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]50[/TD]
[TD]0.93[/TD]
[/TR]
[TR]
[TD]0.93[/TD]
[TD]75[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]50[/TD]
[TD]0.9[/TD]
[/TR]
[TR]
[TD]0.9[/TD]
[TD]75[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]25[/TD]
[TD]0.87[/TD]
[/TR]
[TR]
[TD]0.87[/TD]
[TD]50[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]25[/TD]
[TD]0.84[/TD]
[/TR]
[TR]
[TD]0.84[/TD]
[TD]50[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]10[/TD]
[TD]0.81[/TD]
[/TR]
[TR]
[TD]0.81[/TD]
[TD]25[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]5[/TD]
[TD]0.78[/TD]
[/TR]
[TR]
[TD]0.78[/TD]
[TD]25[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]5[/TD]
[TD]0.75[/TD]
[/TR]
[TR]
[TD]0.75[/TD]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]5[/TD]
[TD]0.72[/TD]
[/TR]
[TR]
[TD]0.72[/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]10[/TD]
[TD]0.69[/TD]
[/TR]
[TR]
[TD]0.69[/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]20[/TD]
[TD]0.66[/TD]
[/TR]
[TR]
[TD]0.66[/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]50[/TD]
[TD]0.63[/TD]
[/TR]
[TR]
[TD]0.63[/TD]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]50[/TD]
[TD]0.6[/TD]
[/TR]
[TR]
[TD]0.6[/TD]
[TD]20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]50[/TD]
[TD]0.57[/TD]
[/TR]
[TR]
[TD]0.57[/TD]
[TD]50[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]50[/TD]
[TD]0.54[/TD]
[/TR]
[TR]
[TD]0.54[/TD]
[TD]50[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]50[/TD]
[TD]0.51[/TD]
[/TR]
[TR]
[TD]0.51[/TD]
[TD]50[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]50[/TD]
[TD]0.48[/TD]
[/TR]
[TR]
[TD]0.48[/TD]
[TD]50[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]50[/TD]
[TD]0.45[/TD]
[/TR]
[TR]
[TD]0.45[/TD]
[TD]50[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]50[/TD]
[TD]0.42[/TD]
[/TR]
[TR]
[TD]0.42[/TD]
[TD]50[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]50[/TD]
[TD]0.39[/TD]
[/TR]
[TR]
[TD]0.39[/TD]
[TD]50[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]50[/TD]
[TD]0.36[/TD]
[/TR]
[TR]
[TD]0.36[/TD]
[TD]50[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]50[/TD]
[TD]0.33[/TD]
[/TR]
[TR]
[TD]0.33[/TD]
[TD]50[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]50[/TD]
[TD]0.3[/TD]
[/TR]
[TR]
[TD]0.3[/TD]
[TD]50[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]50[/TD]
[TD]0.27[/TD]
[/TR]
[TR]
[TD]0.27[/TD]
[TD]50[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]50[/TD]
[TD]0.24[/TD]
[/TR]
[TR]
[TD]0.24[/TD]
[TD]50[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0.21[/TD]
[/TR]
[TR]
[TD]0.21[/TD]
[TD]50[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0.18[/TD]
[/TR]
[TR]
[TD]0.18[/TD]
[TD]50[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0.15[/TD]
[/TR]
[TR]
[TD]0.15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0.12[/TD]
[/TR]
[TR]
[TD]0.12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Thanks for your time and consideration.