Forum Discussion
Formula Help on doing calculation after looking up inside a table
Could someone help me on this formula I have been stuck on? In my sheet, I want to put the formula in cell C21 (green cell). I want to use the number in cell C19 to look up in the tables from A12:B16, then once found where the C19 number falls in table take that C19 times the numbers in C12-C16. For example, based on the my numbers, I want to take C19 * C13 as the end result. Make sense?
5 Replies
mtarler Thank you for the reply. I don;t think this formula works if the number in C19 changes and falls between one of the other gates. I am looking for the formula that will look at C19 and then look at the ranges I have in A12:B16, figure out the correct level, then multiply the number in column C times teh C19.
In my example, the C19 is 25,602,841. That falls in row 13 between 25,000,000 and 29,999,999. So I need the formula to take C19 x the .0065 in row 13. If the number in C19 was 36,000,000 then I need the formula to take row 15 of .0055 times the C19 number. Does this help explain?
Driven1 did you try it? part of the VLOOKUP() function has an option on the range lookup to be approximate or exact. the default is to assume the values are in ascending order and it will use the largest value that is less than the lookup value. That is why I mentioned you might need something to catch cases when the lookup value is less than the smallest value.
If you can't guarantee or know the values/ranges aren't in ascending order then you can use:
=IFERROR(IFS(C19>A16,C16,INDEX(C12:C16,MATCH(1,(A12:A16<=C19)*(B12:B16>=C19),0))),0)*C19
