Forum Discussion
Formula Help on doing calculation after looking up inside a table
Driven1 I believe this is what you want:
=VLOOKUP(C19,A12:C16,3)*C19but you may want to do something in case the number in c19 is <20M like:
=IFERROR(VLOOKUP(C19,A12:C16,3)*C19,0)
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
Thank you. Yes I did try it and it did not work. It gave me an error and the auto correct was wrong. Using your idea though, I did =VLOOKUP(C19,A12:C16,3)*C19 and that seems to work. Thank you for the idea and getting me so close. As for the ranges under 20,000,000, that will never happen so I don't need the ERROR option. Thanks again for the help. mtarler
