Home

Excel formula help

Dan101
New Contributor

Hi new to excel 

What I am looking to do is build a calculator  that has variables .

I think it will be a vlookup but don't know how to do it .

calculator is a variable pricing one  variables are 

0-605     if number between these is entered I need it to select this range and same for the next 3 

606-900

901-1200

1201-1400. 

 

any help or pointing in the right direction would be gratefully appreciated 

 

Many thanks in advance 

Dan

 

3 Replies

Hi Dan,

 

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

 

VLOOKUP Approximate Match Example.png

 

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

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

Hi Dan,

 

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

=VLOOKUP(A2,E2:G6,3)

VLOOKUP Approximate Match.png

 

 

Regards,

Haytham

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies