See if the number between the range in column A & B works, and then multiply the original number by the number in column D of the same row

Question

Here is my challenge. I need to enter a value C16, and then determine what range of numbers is the number of falls in the column A and Bed and . I got part of the way with the following command: =VLOOKUP(C16,$A:$B,TRUE)but it always displays the value of the right line, which is in column A .
What I really want to do is enter the value in C16, determine which row it falls into, and then multiply the value in C16by the number in column D of the same row by which the value was found, and output this answer. <sh> The following is an example of column A - D and lines 2-5. Suppose I entered a value of 10 in C16, it should identify what falls into the range, which on line 1 then multiplies the value inC16, which in this case is 10 in this case D1, which leads to the exit of $ 1690.00

1       99      1    $169.00 
100     499     1    $127.00 
500     1,999   1    $78.00 
2,000   4,999   1    $58.00 

Any help is much appreciated!

Answer 1

I simulated cell C16 using Cell F2, and output in cell G2. The formula for G2 in the above example =SUMPRODUCT(($F$2>=$A1:$A4)*($F$2<=$B1:$B4)*$F$2*($D1:$D4))is This displays $ 0.00 if C16 is left blank. Hope this helps

Answer 2

If your numbers in column A are always ordered, try the following:

=LOOKUP(C16,A:D)*C16

As @barry houdini mentions, this formula assumes it is C16less than or equal to 4999 (the maximum value in column B of your table data)