The disadvantage with the INDEX+SUMPRODUCT+ROW formula is that you need start and end values, the other formulas use the start values also as end range values. Some formulas require you to have the lookup range sorted to function properly, the INDEX+SUMPRODUCT+ROW alternative is the only way to go if you can't sort the values. The following table shows the differences between the formulas presented in this article. You can use the techniques described in this article to calculate discount percentages based on price intervals or linear results based on the lookup value.Ĭheck out the LOOKUP category to find more interesting articles.
There is a file for you to get, at the end of this article, which contains all the formula examples in a worksheet each. I have made a video that explains the LOOKUP function in context to this article, if you are interested. They all have their pros and cons and I will discuss those in great detail, they can be applied to not only numerical ranges but also text ranges and date ranges as well. If value in range then return value - INDEX + MATCH.If value in range then return value - VLOOKUP function.
0 kommentar(er)
