Valu Between two number

[Teeboy]

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

 

[Fergus]

Ummm. I usually assume (possibly naively) that an OP - in this case a relative newcomer to the Board - would prefer that a formula posted within a response to his/her question returns a correct answer rather than simply being in the correct form but returning an incorrect answer.

Having said that, the original post was, in the absence of a HTML snap-shot, somewhat imprecise; referring as it did to columns "A B D" followed by what could have been three or four columns of data depending on how you view the first column of numbers 1 to 6. This uncertainty probably led to the difference in the formulae posted by Sitaram and me. For this very reason, in posting a formula at variance with Sitarams', I explained and showed the layout that I had assumed and from which I had derived the formula.

Since the OP has not responded we cannot at present judge what his intentions were or if either of the formulae produced the desired result.

 

[Teeboy]

Hi guys

Sorry i hae not responded but i have been on hols

Thanks for all your input it has been really usefull I didnot realise the various ways of using the vlookup. I have been using the format
=vlookup(,$a$2:$f$50,3,false) (numbers inserted for asthetic reasons of course) and nothing else.