Google Sheets: Return cell value in a range/array based on multiple criteria (including comparison of value between two numbers)
Would really appreciate some help with this issue which I've been struggling with for a while. I believe the answer is somewhere in Match, Index, Indirect, ArrayFormula but I'm struggling to find it.
So my main goal is to get the cost of a product based on 3 (technically two) evaluations. The input that I have available is the Tier and the weight.
| Example | Tier | Weight kg | Prod Cost |
|---|---|---|---|
| Example 1 | SPC | 0.65 | ? |
| Example 2 | STE | 0.15 | ? |
The reference table from which the product cost will be derived is below:
| Row | Tier | Min Wt(kg) | Max Wt (kg) | Prod Cost |
|---|---|---|---|---|
| 1 | SME | 0.00 | 0.10 | 4.0 |
| 2 | STE | 0.00 | 0.10 | 4.5 |
| 3 | STE | 0.10 | 0.25 | 4.7 |
| 4 | STE | 0.25 | 0.50 | 5.0 |
| 5 | LE | 0.00 | 1.00 | 5.5 |
| 6 | SPC | 0.00 | 0.25 | 5.7 |
| 7 | SPC | 0.25 | 0.50 | 6.0 |
| 8 | SPC | 0.50 | 1.00 | 6.5 |
| 9 | SPC | 1.00 | 1.50 | 7.0 |
In order to get the product cost I need to evaluate the tier and then the evaluate which range of min and max weight the product weight falls into (for that tier).
So taking example 1, the tier is SPC which makes Row 6 to 9 valid. Then the weight is 0.65 which is between the min and max weight values of 0.5kg and 1.0kg in row 8. Therefore the product cost is 6.5.
Kindly note that both criteria are important as the weight ranges overlap between tiers.
Any help on how to evaluate this would be highly appreciated.
Thanks
try:
=INDEX(IFNA(VLOOKUP(VLOOKUP(F2:F, {UNIQUE(FILTER(A2:A, A2:A<>"")),
SEQUENCE(COUNTUNIQUE(A2:A))*10000}, 2, 0)+G2:G, {
VLOOKUP(A2:A, {UNIQUE(FILTER(A2:A, A2:A<>"")),
SEQUENCE(COUNTUNIQUE(A2:A))*10000}, 2, 0)+B2:B, D2:D}, 2, 1)))
how it works:
