Populate cell based on RANGE of CALCULATED values.

[pommesmitmayo]

My request is very similar to the question asked here;

Want to populate a cell with specific text based on a range of values in another cell

I need a formula that look at the drop down item selected from the drop down menu in column E, and based on what it sees it populate the cell in column F with a specific text. In the example pic the possible selections from the drop down are JC1 to JC8, for anything in the range I would want...

www.mrexcel.com

But I'd like to add a twist.
So, supposing I'm cook, (I'm not), and I have a range of different serving dishes I can use depending on how much of a certain liquid I'm serving.
Now, in cell A1 I'd have the flow rate of a tap.
Cell B1 would be the length of time that tap is open for.
C1 would contain the product of these two cells and indicate how much water had passed. It could be anywhere between 100ml and 100L.
D1 would be where my output is and would return the name of one of six vessels;

• Vessel 1, holds between 0 and 500ml.
• Vessel 2, holds between >500ml and 1L.
• Vessel 3, holds between >1L and 5L.
• Vessel 4, holds between >5L and 10L.
• Vessel 5, holds between >10L and 35L.
• Vessel 6, holds between >35L and 100L.

For basic calls like this I'd usually look to VLOOKUP, but is this possible in this instance where the calculation might return 17,236ml which is nowhere near the values you'd usually find in a look up table??
You can see that I'd need vessel 5 for this and although Vessel 6 would also I work, I don't want this value returned.

Any pointers would be fab.

Thankyou.

 

[StephenCrump]

It sounds like you just need a simple VLOOKUP?

	A	B	C
1
2		Litres	Vessel
3		0	1
4		0.5	2
5		1	3
6		5	4
7		10	5
8		35	6
9		100	Too much!
10
11		My Quantity	17.236
12		Vessel to use	5
Sheet1

Cell Formulas
Range	Formula
C12	C12	=VLOOKUP(C11,B3:C9,2,1)

You might want to play with the boundary limits, e.g. if you had 9.9 litres, say, it would just fit in vessel 4, but you might prefer to put it into vessel 5.

 

[pommesmitmayo]

Oh right, you mean I kind of had the answer all along?
O notice that in your VLOOK up you don't have the True or False values. Why is that?

 

[StephenCrump]

I've just used 1, instead of TRUE.

TRUE is the default value for the range_lookup argument if it's not specified, so I could have omitted the argument:

C12	=VLOOKUP(C11,B3:C9,2)

But I prefer to put it in, as then I'm totally clear in my mind whether I want an approximate match or an exact match.

There are plenty of posts on this forum from people getting caught out in the other direction. Forgetting to specify FALSE or 0 for an exact match, and then wondering why their VLOOKUP is returning the wrong value.

 

[pommesmitmayo]

I've just used 1, instead of TRUE.

TRUE is the default value for the range_lookup argument if it's not specified, so I could have omitted the argument:

C12	=VLOOKUP(C11,B3:C9,2)

But I prefer to put it in, as then I'm totally clear in my mind whether I want an approximate match or an exact match.

There are plenty of posts on this forum from people getting caught out in the other direction. Forgetting to specify FALSE or 0 for an exact match, and then wondering why their VLOOKUP is returning the wrong value.

Oh I see. Well you learn something every day.
VLookup is giving me grief at the moment, but I'll experiment and see what gives. There are formulas in the sheet to arrive at some of the values for the lookup function but for some reason the sheet doesn't calculate properly.

 

[pommesmitmayo]

Hi again, the sheet is almost there. It doesn't seem to work exactly though.
Can I send to you for comment pls?

If so, shall I PM or up load here?

 

[Fluff]

You cannot upload a sheet here, either use the XL2BB add-in to post a mini-sheet as Stephen did in post#2 or upload to a share site & mark for sharing & then post the link you are given.

 

[pommesmitmayo]

You cannot upload a sheet here, either use the XL2BB add-in to post a mini-sheet as Stephen did in post#2 or upload to a share site & mark for sharing & then post the link you are given.

No problem, will do. Thankyou.

 

[pommesmitmayo]

Hi all sorry about the delay. Pls see below;

Loading Google Sheets

 

[StephenCrump]

Hi again, the sheet is almost there. It doesn't seem to work exactly though.

Here's your workbook. What should be happening?

test.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1		CUPS_protect1
2		Box size	12							1	2	3
3		Cans per box	6						Type 1	2.5	2.5	4
4		Boxes per pallet	40						Type 2	6	2.5	4
5		Cans per pallet	240						Type 1	16	16	25
6		Number of pallets	3						Type 2	35	16	25
7		Total box count	120
8		Total can count	720
9		Required volume	40000.00
10		Losses	99.50%
11		Actual volume needed	40201.00503
12		Volume/can	55.83
13		Volume/box	335.01
14		Min ranges	1.67
15		C17 / C6	33.43
16		C5 * C14	400.80
17		C11 / C6	100.30
18
19		Box size	Cans per box
20		12	6
21		6	3
22		4	2
23		2	1
24
25		Min set up ranges
26		1.6
27		1.65
28		1.67
29		1.7
30		1.75
31		1.78
32		1.8
33		1.83
34
35					1.50
36		30.00	24.00	19.50	2.50
37		40.00	34.00	27.00	4.00
38		54.00	46.00	38.00	6.00
39		72.00	61.00	51.00	10.00
40		100.00	87.00	72.00	16.00
41		135.00	118.00	99.00	25.00
42		179.00	158.00	133.00	35.00
43		225.00	198.00	168.00	50.00
44		283.00	250.00	214.00	70.00
45		354.00	314.00	271.00	95.00
46		425.00	378.00	328.00	120.00
47		501.00	446.00	388.00	150.00
48		578.00	515.00	449.00	185.00
49		659.00	587.00	514.00	240.00
50		795.00	705.00	618.00	300.00
51		923.00	813.00	713.00	400.00
52		1120.00	977.00	857.00	500.00
53		1270.00	1085.00	950.00	630.00
54		1460.00	1213.00	1139.00	630.00
55					0.50
56					0.75
57					1.00
58					1.50
59			24.00	19.50	2.50
60			34.00	27.00	4.00
61		30.00	46.00	38.00	6.00
62		39.00	61.00	51.00	10.00
63		51.00	87.00	72.00	16.00
64		73.00	118.00	99.00	25.00
65		97.00	158.00	133.00	35.00
66		140.00	198.00	168.00	50.00
67		175.00	250.00	214.00	70.00
68		216.00	314.00	271.00	95.00
69		258.00	378.00	328.00	120.00
70		302.00	446.00	388.00	150.00
71		347.00	515.00	449.00	185.00
72		394.00	587.00	514.00	240.00
73		471.00	705.00	618.00	300.00
74		541.00	813.00	713.00	400.00
75		644.00	977.00	857.00	500.00
76		738.00	1085.00	950.00	630.00
77		861.00	1213.00	1139.00	630.00
78
CALC

Cell Formulas
Range	Formula
C3	C3	=VLOOKUP(C2,B19:KC23,2,FALSE)
J3	J3	=VLOOKUP(C15,B36:E54,4,1)
K3	K3	=VLOOKUP(C15,C36:E54,3,1)
L3	L3	=VLOOKUP(C15,D36:E54,2,1)
J4	J4	=VLOOKUP(C15,B55:E77,4,1)
K4	K4	=VLOOKUP(C15,C55:E77,3,1)
L4	L4	=VLOOKUP(C15,D55:E77,2,1)
J5	J5	=VLOOKUP(C17,B36:E54,4,1)
K5	K5	=VLOOKUP(C17,C36:E54,3,1)
L5	L5	=VLOOKUP(C17,D36:E54,2,1)
J6	J6	=VLOOKUP(C17,B55:E77,4,1)
K6	K6	=VLOOKUP(C17,C55:E77,3,1)
L6	L6	=VLOOKUP(C17,D55:E77,2,1)
C5	C5	=C3*C4
C7	C7	=C4*C6
C8	C8	=C5*C6
C11	C11	=C9/C10
C12	C12	=C11/C8
C13	C13	=C11/C7
C15	C15	=C17/C6
C16	C16	=C5*C14
C17	C17	=C11/C16

Cells with Data Validation
Cell	Allow	Criteria
C2	List	=$B$20:$B$23
C14	List	=$B$26:$B$33