Index/ match and match??

[Tina Do]

Hi everyone,

Appreciate if you can advise the formula that is applicable for this scenario.


1. Sheet 1 - table 1
This table shows the price of apples at different minimum order qty (MOQ) in Q2

[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]Types of apple[/TD]
[TD]MOQ 1[/TD]
[TD]Unit price 1[/TD]
[TD]MOQ 2[/TD]
[TD]Unit price 2[/TD]
[TD]MOQ 3[/TD]
[TD]Unit price 3[/TD]
[/TR]
[TR]
[TD]Apple 1[/TD]
[TD]500[/TD]
[TD]2.41[/TD]
[TD]1000[/TD]
[TD]1.92[/TD]
[TD]2000[/TD]
[TD]1.62[/TD]
[/TR]
[TR]
[TD]Apple 2 [/TD]
[TD]50[/TD]
[TD]1.65[/TD]
[TD]100[/TD]
[TD]1.36[/TD]
[TD]2000[/TD]
[TD]1.26[/TD]
[/TR]
[TR]
[TD]Apple 3[/TD]
[TD]30[/TD]
[TD]1.73[/TD]
[TD]60[/TD]
[TD]1.46[/TD]
[TD]90[/TD]
[TD]1.27[/TD]
[/TR]
[TR]
[TD]Apple 4[/TD]
[TD]250[/TD]
[TD]3.50[/TD]
[TD]500[/TD]
[TD]3.20[/TD]
[TD]1000[/TD]
[TD]2.82[/TD]
[/TR]
[TR]
[TD]Apple 5[/TD]
[TD]250[/TD]
[TD]2.13[/TD]
[TD]500[/TD]
[TD]1.84[/TD]
[TD]1000[/TD]
[TD]1.46[/TD]
[/TR]
[TR]
[TD]Apple 6[/TD]
[TD]1000[/TD]
[TD]1.78[/TD]
[TD]2000[/TD]
[TD]1.58[/TD]
[TD]3000[/TD]
[TD]1.29[/TD]
[/TR]
[TR]
[TD]Apple 7[/TD]
[TD]250[/TD]
[TD]1.65[/TD]
[TD]500[/TD]
[TD]1.36[/TD]
[TD]1000[/TD]
[TD]1.26[/TD]
[/TR]
</tbody>[/TABLE]


2. Sheet 2 - table 2

This table shows the price of apples at different MOQ in Q3 and I need to match them against apple type and MOQ in Q2 to to retrieve the price from sheet 1 - table 1.

Note that the rows are not in sequence as in table 1 and the MOQ may be different from table 1.


[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]Types of apple[/TD]
[TD]MOQ 1[/TD]
[TD]Unit price 1 - Q3[/TD]
[TD]Unit price 1 - Q2[/TD]
[TD]MOQ 2[/TD]
[TD]Unit price 2 - Q3[/TD]
[TD]Unit price 1 - Q2[/TD]
[TD]MOQ 3[/TD]
[TD]Unit price 3 - Q3[/TD]
[TD]Unit price 3 - Q2[/TD]
[/TR]
[TR]
[TD]Apple 4[/TD]
[TD]250[/TD]
[TD]1.92[/TD]
[TD]??[/TD]
[TD]1000[/TD]
[TD]2.82[/TD]
[TD]??[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Apple 1[/TD]
[TD]500[/TD]
[TD]1.92[/TD]
[TD]??[/TD]
[TD]1000[/TD]
[TD]1.62[/TD]
[TD]??[/TD]
[TD]2000[/TD]
[TD]1.59[/TD]
[TD]??[/TD]
[/TR]
[TR]
[TD]Apple 6[/TD]
[TD]1000[/TD]
[TD]1.78[/TD]
[TD]??[/TD]
[TD]2000[/TD]
[TD]1.58[/TD]
[TD]??[/TD]
[TD]3000[/TD]
[TD]1.29[/TD]
[TD]??[/TD]
[/TR]
[TR]
[TD]Apple 2[/TD]
[TD]100[/TD]
[TD]1.36[/TD]
[TD]??[/TD]
[TD]2000[/TD]
[TD]1.26[/TD]
[TD]??[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Apple 3[/TD]
[TD]60[/TD]
[TD]1.46[/TD]
[TD]??[/TD]
[TD]90[/TD]
[TD]1.25[/TD]
[TD]??[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I need to input formula to get the Q2 price in all "??" column. Could anyone help me please? I was not able successful with index and match/ match function. Thanks.

 

[Fluff]

Assuming your data starts in A1, how about
=INDEX(Sheet1!$B$2:$G$8,MATCH($A2,Sheet1!$A$2:$A$8,0),MATCH(LEFT($D$1,FIND(" -",$D$1)-1),Sheet1!$B$1:$G$1,0))

 

[Tina Do]

Hi Fluff,

Apologize but what if the table 2 is in below format? What should be the formula? Thanks.

[TABLE="width: 500"]
<tbody>[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]After
[/TD]
[TD]Before
[/TD]
[TD]
[/TD]
[TD]After
[/TD]
[TD]Before
[/TD]
[TD]
[/TD]
[TD]After
[/TD]
[TD]Before
[/TD]
[/TR]
[TR]
[TD]Types of apple
[/TD]
[TD]MOQ 1
[/TD]
[TD]Unit price 1
[/TD]
[TD]Unit price 1
[/TD]
[TD]MOQ 2
[/TD]
[TD]Unit price 2
[/TD]
[TD]Unit price 1
[/TD]
[TD]MOQ 3
[/TD]
[TD]Unit price 3
[/TD]
[TD]Unit price 3
[/TD]
[/TR]
[TR]
[TD]Apple 4
[/TD]
[TD]250
[/TD]
[TD]1.92
[/TD]
[TD]??
[/TD]
[TD]1000
[/TD]
[TD]2.82
[/TD]
[TD]??
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Apple 1
[/TD]
[TD]500
[/TD]
[TD]1.92
[/TD]
[TD]??
[/TD]
[TD]1000
[/TD]
[TD]1.62
[/TD]
[TD]??
[/TD]
[TD]2000
[/TD]
[TD]1.59
[/TD]
[TD]??
[/TD]
[/TR]
[TR]
[TD]Apple 6
[/TD]
[TD]1000
[/TD]
[TD]1.78
[/TD]
[TD]??
[/TD]
[TD]2000
[/TD]
[TD]1.58
[/TD]
[TD]??
[/TD]
[TD]3000
[/TD]
[TD]1.29
[/TD]
[TD]??
[/TD]
[/TR]
[TR]
[TD]Apple 2
[/TD]
[TD]100
[/TD]
[TD]1.36
[/TD]
[TD]??
[/TD]
[TD]2000
[/TD]
[TD]1.26
[/TD]
[TD]??
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Apple 3
[/TD]
[TD]60
[/TD]
[TD]1.46
[/TD]
[TD]??
[/TD]
[TD]90
[/TD]
[TD]1.25
[/TD]
[TD]??
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Fluff]

How about
=INDEX(Sheet1!$B$2:$G$8,MATCH($A2,Sheet1!$A$2:$A$8,0),MATCH($D$1,Sheet1!$B$1:$G$1,0))

 

[Tina Do]

Hi Fluff, your formula doesn't take into consideration of the MOQ. For example, that formula will return the price for Apple 2/ unit price 1 as 1.65. But that is wrong because 1.65 is the price for MOQ 50, not 100 which is the first MOQ of Apple 2 in table 2.

Would you be able to advise a formula that can take into consideration of the MOQ? Thanks.

 

[Fluff]

Maybe
=INDEX(Sheet1!$B$2:$G$8,MATCH($A2,Sheet1!$A$2:$A$8,0),MATCH($D$1,Sheet1!$B$1:$G$1,0))/INDEX(Sheet1!$B$2:$G$8,MATCH($A2,Sheet1!$A$2:$A$8,0),MATCH($D$1,Sheet1!$B$1:$G$1,0)-1)*B2