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

[rtr1811]

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.

 

[Fluff]

How about

=VLOOKUP(K4,$B$4:$E$25,4,1)

 

[rtr1811]

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.

A	B	C	D	E F Merged	G H Merged	I	J	K	L Formula Output
From	To
1	2	3	4	5	6
1	1	10	10	7			1	1	7
2	11	15	5	6			1	2	7
3	16	16	1	7			1	3	7
4	17	17	1	6			1	4	7
5	18	19	2	7			1	5	7
6	20	21	2	6			1	6	7
7	22	22	1	7			1	7	7
8	23	24	2	6			1	8	7
9	25	29	5	7			1	9	7
10	30	30	1	6			1	10	7
11	31	51	21	6			1	11	6
12	52	52	1	7			1	12	6
13	53	54	2	6			1	13	6
14	55	59	5	7			1	14	6
15	60	60	1	6			1	15	6
16	61	61	1	7			1	16	7
17	62	70	9	6			1	17	6
18	71	74	4	7			1	18	7
19	75	79	5	6			1	19	7
20	80	82	3	7			1	20	6
21	83	84	2	6			1	21	6
22	85	87	3	7			1	22	7

Can you please help me to get around this error?

 

[Fluff]

What error? It looks to be doing exactly what you asked for.

 

[rtr1811]

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]

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]

I think this formula may also work for you...

=IF(MEDIAN(B4,C4,K4)=K4,E4,"")

 

[rtr1811]

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]

Why have you selected up to Column E in the formula?

Because you said you needed to return the value from col E.