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:
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
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?
 
Upvote 0
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))))
 
Upvote 0
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)
 
Upvote 0
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?
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,338
Members
448,570
Latest member
rik81h

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