Valu Between two number

Teeboy

Board Regular
Joined
Jun 12, 2003
Messages
91
Hi All

What formula/s do i need to return a value based on a number being between two other numbers.

E.G.
A B D
1 £- £1,000 12
2 £1,000 £3,000 14
3 £3,000 £4,000 16
4 £4,000 £6,000 18
5 £6,000 £1,000 20
6 any value

IF A6 lies between any of the values in columns A & B then return the relevant value in D

:eek:
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
Teeboy said:
Hi All

What formula/s do i need to return a value based on a number being between two other numbers.

E.G.
A B D
1 £- £1,000 12
2 £1,000 £3,000 14
3 £3,000 £4,000 16
4 £4,000 £6,000 18
5 £6,000 £1,000 20
6 any value

IF A6 lies between any of the values in columns A & B then return the relevant value in D

:eek:

Some observations to note: A1 houses probably 0. B5 has a value incongruent with the set up A:B suggests. Comments?
 

dr_shivan

Active Member
Joined
Jan 3, 2005
Messages
298

ADVERTISEMENT

you could try putting this in cell D6

=IF(A6<=B1,D1,IF(A6<=B2,D2,IF(A6<=B3,D3,IF(A6<=B4, D4,D5))))
 

Fergus

Well-known Member
Joined
Mar 10, 2004
Messages
1,174
Hi Teeboy,

Having adjusted your ranges to avoid overlaps I get this, does it suit?
Book2
ABCD
1$0$1,00012
2$1,001$3,00014
3$3,001$4,00016
4$4,001$6,00018
5$6,001$8,00020
6$2,99914
Sheet3


Formula in D6 is =VLOOKUP(A6, A1:D5,4,1)
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201

ADVERTISEMENT

Fergus said:
Hi Teeboy,

Having adjusted your ranges to avoid overlaps I get this, does it suit?

...

Formula in D6 is =VLOOKUP(A6, A1:D5,4,1)

Didn't Sitaram already provided the same answer with that assumption?
 

Fergus

Well-known Member
Joined
Mar 10, 2004
Messages
1,174
Aladin,

As far as I could see No.

Sitaram's lookup array was B1:D5 and col 3. As far as I could see this would return the number from col D for the range below the range within which A6 falls, or am I misinterpreting his formula?
 

Fergus

Well-known Member
Joined
Mar 10, 2004
Messages
1,174
Further to the above I have enormous respect for Sitaram, who was the first person to ever help me on this Board and who patiently taught me my first steps in vba, and of course Aladin, your huge knowledge is understood by all who use this Board. However, with respects, please see below:
Book2
ABCDE
1$0$1,00012
2$1,001$3,00014
3$3,001$4,00016
4$4,001$6,00018
5$6,001$8,00020
6$2,50014<--Mine
712<--Sitaram
Sheet3
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
I was referring to its form... In that respect it's a correct formula. If you didn't take that in that way, I can understand why you deemed desirable to post.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,076
Messages
5,599,627
Members
414,326
Latest member
kfg1287

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