Forum Discussion

Haytham Amairah's avatar
Haytham Amairah
Silver Contributor
Jan 06, 2019

Hi Dan,

 

Please check out the below example which I think is similar to what you looking for.

 

 

This formula in cell B2 is this:

=VLOOKUP(A2,E2:G6,3)*A2

 

The first part of the formula searches the number in cell A2 in which range it falls in the lookup table.

After matching the number in the range, it will return the corresponding percentage of this range.

After that, the formula multiples this percentage by the same number in cell A2.

 

For example, if the number in cell A2 is 12, this number falls in the range (11 - 20), and the corresponding percentage of this range is 30%.

Therefore, the VLOOKUP will return this percentage and multiply it by the value of cell A2 which is 12.

 

Hope that helps

Dan101's avatar
Dan101
Copper Contributor
Jan 06, 2019

Hi

Thank you for the info if I didn't want to times & just replace with a set value
say 1-10 = 34
11-20 =40
21-30=54
31-40 =60
how would I edit the formula ?

many thanks Dan

  • Haytham Amairah's avatar
    Haytham Amairah
    Silver Contributor
    Jan 07, 2019

    Hi Dan,

     

    Simply, remove the multiplication part from the formula as follows:

    =VLOOKUP(A2,E2:G6,3)

     

     

    Regards,

    Haytham

Resources