# Excel formula to compare whether a cell value lies between two numbers and copy a value accordingly

#### rtr1811

##### New Member
I have to check a particular cell value lies between two column values and copy a corresponding a cell value accordingly. E.g. Please refer the image i have attached here with.
I have to check the value in column K, if it lies between B and C, I have to copy the in Column E to Column L. In short, For the numbers 1 to 10 in column K, I have to copy the number in column E4(i.e 7) and for the numbers 11 to 15 in column K, I have to copy the number in column E5(i.e. 6) and so on. Can any body help me with a formula to achieve this? Thanks in advance.

### Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

#### Fluff

##### MrExcel MVP, Moderator
Excel Formula:
``=VLOOKUP(K4,\$B\$4:\$E\$25,4,1)``

#### rtr1811

##### New Member
Thank you for your reply. It copies the values. But when I filter the column E for "6" it shows me 11 and the VLOOKUP output for '6' is only 8. Similarly If I filter Column E for "7" it shows me 11. But we have 14 "7s" in the output column. Here is the table for your reference.

ABCDE F MergedG H MergedIJKL
Formula Output
FromTo
123456
1110107117
2111556127
3161617137
4171716147
5181927157
6202126167
7222217177
8232426187
9252957197
103030161107
1131512161116
125252171126
135354261136
145559571146
156060161156
166161171167
176270961176
187174471187
197579561197
208082371206
218384261216
228587371227

Can you please help me to get around this error?

#### Fluff

##### MrExcel MVP, Moderator
What error? It looks to be doing exactly what you asked for.

#### rtr1811

##### New Member

Sorry. In my real sheet, I have "Total" in the last row which might have caused some problems. It's working now. I have one more doubt in the formula. Can you please explain? I want to the numbers in column K against the numbers in Column B & C. Why have you selected up to Column E in the formula? Is it really needed? Selecting up to Column C is not enough? Thank you.

#### rtr1811

##### New Member
Sorry. In my real sheet, I have "Total" in the last row which might have caused some problems. It's working now. I have one more doubt in the formula. Can you please explain? I want to compare the numbers in column K against the numbers in Column B & C. Why have you selected up to Column E in the formula? Is it really needed? Selecting up to Column C is not enough? Thank you.

#### Rick Rothstein

##### MrExcel MVP

I think this formula may also work for you...
Excel Formula:
``=IF(MEDIAN(B4,C4,K4)=K4,E4,"")``

#### rtr1811

##### New Member
Thank you for your reply. It is not working for the entire Column. It works only for the first cell. When I drag the formula, some of the remaining cells are empty and some cells are filled with 0.

#### Fluff

##### MrExcel MVP, Moderator
Why have you selected up to Column E in the formula?
Because you said you needed to return the value from col E.

Replies
16
Views
67
Replies
0
Views
32
Replies
9
Views
134
Replies
23
Views
2K
Replies
0
Views
119

1,133,244
Messages
5,657,587
Members
418,401
Latest member
B_A_M155

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

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