Between

[Hsibbs]

What is the best way to do a between? that is, is this figure between to other figures. I have a table like this

A 10 20
B 21 30
C 31 40
D 41 50

If a number in another cell is 33, I want it to return C

I've used IF(AND >= <= but that seems v complicated!!! Is there a function I could use?

 

[onlyadrafter]

Hello,

If your data starts in A1, and 33 is in A7, then try

=OFFSET(A1,MATCH(A7,B1:B4,1)-1,0)

 

[Andrew Poulsom]

Try:

=INDEX(A:A,MATCH(D1,B:B))

wher D1 contains 33.

 

[Hsibbs]

I'm still stuck, cam I ask for more help.

My data table is :

Col A Colb(empty) ColmnC ColmnD
row2 F 9,999,999,999 1,500,000,000
row3 E 1,500,000,000 1,250,000,000
row4 D 1,250,000,000 1,000,000,000
row 5 C 1,000,000,000 750,000,000
row 6 B 750,000,000 500,000,000
row 7 A 300,000,000 200,000,000

my cell to check is is in K3 and says 1,234,781,333, so I want my frmula to get "D"


Hope you can help

 

[onlyadrafter]

Hello,

apologies to AP for stealing his thunder (and formula)

but try

=INDEX(A:A,MATCH(D1,B:B,-1))

my formula can also be amended to

=OFFSET(A1,MATCH(A11,B1:B7,-1)-1,0)

but I'd use the former.

 

[Andrew Poulsom]

It's always helpful to post the actual data first, rather than some imaginary data that doesn't fit the actual scenario. Try:

=INDEX(A:A,MATCH(K3,C:C,-1))

 

[Hsibbs]

Its given me back "E" rather than "D" ????? what does the -1 mean?

 

[Andrew Poulsom]

The formula I posted:

=INDEX(A:A,MATCH(K3,C:C,-1))

returns D.

If match_type is -1, MATCH finds the smallest value that is greater than or equal to lookup_value. Lookup_array must be placed in descending order: TRUE, FALSE, Z-A,...2, 1, 0, -1, -2,..., and so on.

 

[Hsibbs]

Indeed it does. I don't quite know what I've done, but it works, thank you