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

Types of apple	MOQ 1	Unit price 1	MOQ 2	Unit price 2	MOQ 3	Unit price 3
Apple 1	500	2.41	1000	1.92	2000	1.62
Apple 2	50	1.65	100	1.36	2000	1.26
Apple 3	30	1.73	60	1.46	90	1.27
Apple 4	250	3.50	500	3.20	1000	2.82
Apple 5	250	2.13	500	1.84	1000	1.46
Apple 6	1000	1.78	2000	1.58	3000	1.29
Apple 7	250	1.65	500	1.36	1000	1.26

<tbody>
</tbody>

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.

Types of apple	MOQ 1	Unit price 1 - Q3	Unit price 1 - Q2	MOQ 2	Unit price 2 - Q3	Unit price 1 - Q2	MOQ 3	Unit price 3 - Q3	Unit price 3 - Q2
Apple 4	250	1.92	??	1000	2.82	??
Apple 1	500	1.92	??	1000	1.62	??	2000	1.59	??
Apple 6	1000	1.78	??	2000	1.58	??	3000	1.29	??
Apple 2	100	1.36	??	2000	1.26	??
Apple 3	60	1.46	??	90	1.25	??

<tbody>
</tbody>

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.

		After	Before		After	Before		After	Before
Types of apple	MOQ 1	Unit price 1	Unit price 1	MOQ 2	Unit price 2	Unit price 1	MOQ 3	Unit price 3	Unit price 3
Apple 4	250	1.92	??	1000	2.82	??
Apple 1	500	1.92	??	1000	1.62	??	2000	1.59	??
Apple 6	1000	1.78	??	2000	1.58	??	3000	1.29	??
Apple 2	100	1.36	??	2000	1.26	??
Apple 3	60	1.46	??	90	1.25	??

<tbody>
</tbody>

 

[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