Please point me in the right direction for looking up within

Wayne_Sadler

Board Regular
Joined
Jan 7, 2005
Messages
148
a range I think

He's an example

I type in a value say A1 for example

I want A2 to lookup a varble range ie

if it's between

0 - 100 = 10
100 - 200 = 18
200 - 300 = 25

so A2 would give the answer of 18 if the A1 field said 156 say

I hope I've expalined my self

many thanks Wayne

Any advice welcome
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Try this formula, to be entered in cell A2:

IF(AND(A1>=0,A1<100),10,IF(AND(A1>=100,A1<200),18,IF(AND(A1>=200,A1<300),25,"")))

Post for feedback

Ciao
 
Upvote 0
Re: Please point me in the right direction for looking up wi

Wayne_Sadler said:
a range I think

He's an example

I type in a value say A1 for example

I want A2 to lookup a varble range ie

if it's between

0 - 100 = 10
100 - 200 = 18
200 - 300 = 25

so A2 would give the answer of 18 if the A1 field said 156 say

I hope I've expalined my self

many thanks Wayne

Any advice welcome

One of:

=LOOKUP(A1,{-9.99999999999999E+307,0,100,200},{"",10,18,25})

If 300 is a limiting case...

=IF(N(A1)<=300,LOOKUP(A1,{-9.99999999999999E+307,0,100,200},{"",10,18,25}),"")
 
Upvote 0
I got to say I'm playing but

If seems to WORK :LOL:

Many Many THANKS !!! for your help

I don't know what else to say, but MANY THANKS :)

Wayne
 
Upvote 0
I have one question Aladin Akyurek

Can I get the 10,18,25 to found on sheet 2

The idea being the 10,18,25 are in there own fields on sheet 2 so they can me amended with easy but still be looked up by the sheet 1 page

Many thanks Wayne
 
Upvote 0
Also just thinking about it

Can this be done for a % I tried customizing your code but it did not like it

This is the other part

0.00 > 50.00 = *20% ie 20% of 50 = 10
100. > 200.00 = *10% ie 10% of 200 = 20
300. > Whatever = *5% ie 5% of 300 = 5

But if it between 100 > 200 = 20 + the one before which is 10 = total of 30 if 200 would be entered in as a value.

But I would like the % values in cell on Sheet2 if possiable else string cell command will do..

Many thanks Wayne
 
Upvote 0
Wayne_Sadler said:
I got to say I'm playing but

If seems to WORK :LOL:

Many Many THANKS !!! for your help

I don't know what else to say, but MANY THANKS :)

Wayne

The formula

=IF(N(A1)<=300,LOOKUP(A1,{-9.99999999999999E+307,0,100,200},{"",10,18,25}),"")

references a built-in table.

You could put the values involved in A2:B5 on Sheet2 or on a sheet you could name Admin. Thus;

Put the values from {-9.99999999999999E+307,0,100,200} in A2:A5 and the values fom {"",10,18,25} in B2:B5, with one exception: B2 must house the following formula:

=""

instead of just "".

Now select A2:B5, go to the Name Box on the Formula Bar, type Table, and hit enter. The LOOKUP formula can then be modified to reference Table instead of a built-in table:

=IF(N(A1)<=300,LOOKUP(A1,Table),"")
 
Upvote 0
Wayne_Sadler said:
Also just thinking about it

Can this be done for a % I tried customizing your code but it did not like it

This is the other part

0.00 > 50.00 = *20% ie 20% of 50 = 10
100. > 200.00 = *10% ie 10% of 200 = 20
300. > Whatever = *5% ie 5% of 300 = 5

But if it between 100 > 200 = 20 + the one before which is 10 = total of 30 if 200 would be entered in as a value.

But I would like the % values in cell on Sheet2 if possiable else string cell command will do..

Many thanks Wayne

This question looks different... If I'm following you, you need to look at:

http://www.mcgimpsey.com/excel/taxvariablerate.html

Example applications hereof are also available on this site.
 
Upvote 0
I sort of there but having problems with the NAME BOX bit and the "TABLE"

I Select A2 to B5 I got A2 in the NAME BOX I can type "TABLE" but it does not stay in goes and there no X or Green Tick for the Fx Box

What am I doing wrong ?

I feel a right muppet...

Many thanks wayne
 
Upvote 0
That site looks very very interesting thanks Aladin Akyurek

I'm going to play, there's only one thing it's what I think I trying to achive but not sure if it added together...


I guess it's play time through the SQUARE WINDOW hey LOL :biggrin:

I going to play now he he he

Many thanks Wayne once again
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,192
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top