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

rtr1811

New Member
Joined
Jun 3, 2020
Messages
8
Office Version
  1. 2007
Platform
  1. Windows
Respected Sir/Madam,
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.
ExcelTest.jpg
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
60,325
Office Version
  1. 365
Platform
  1. Windows
How about
Excel Formula:
=VLOOKUP(K4,$B$4:$E$25,4,1)
 
Solution

rtr1811

New Member
Joined
Jun 3, 2020
Messages
8
Office Version
  1. 2007
Platform
  1. Windows
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
Joined
Jun 12, 2014
Messages
60,325
Office Version
  1. 365
Platform
  1. Windows
What error? It looks to be doing exactly what you asked for.
 

rtr1811

New Member
Joined
Jun 3, 2020
Messages
8
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Jun 3, 2020
Messages
8
Office Version
  1. 2007
Platform
  1. Windows
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
Joined
Apr 18, 2011
Messages
37,196
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

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

rtr1811

New Member
Joined
Jun 3, 2020
Messages
8
Office Version
  1. 2007
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,137,151
Messages
5,679,895
Members
419,861
Latest member
AceDaMace

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
Top