# Between

#### Hsibbs

##### Board Regular
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?

### Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Hello,

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

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

Try:

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

wher D1 contains 33.

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

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.

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

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

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.

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

Replies
3
Views
90
Replies
4
Views
150
Replies
20
Views
480
Replies
3
Views
211
Replies
12
Views
412

1,203,174
Messages
6,053,928
Members
444,694
Latest member
JacquiDaly

### 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.

### Which adblocker are you using?

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

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