Please point me in the right direction for looking up within

Wayne_Sadler

Board Regular
Joined
Jan 7, 2005
Messages
146
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
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

chiello

Well-known Member
Joined
Jan 18, 2005
Messages
848
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
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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}),"")
 

Wayne_Sadler

Board Regular
Joined
Jan 7, 2005
Messages
146
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
 

Wayne_Sadler

Board Regular
Joined
Jan 7, 2005
Messages
146

ADVERTISEMENT

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
 

Wayne_Sadler

Board Regular
Joined
Jan 7, 2005
Messages
146
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
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201

ADVERTISEMENT

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),"")
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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.
 

Wayne_Sadler

Board Regular
Joined
Jan 7, 2005
Messages
146
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
 

Wayne_Sadler

Board Regular
Joined
Jan 7, 2005
Messages
146
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,156
Messages
5,600,040
Members
414,357
Latest member
Gemma_R

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
Top