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