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.

 

[pommesmitmayo]

Hi there, Stephen.
So what I was doing was adjusting values ever so slightly to watch the threshold at which the values in J3 to L6 changed.
For example making changes such that if the value in C15 needed to be 40 as per B37 I could see a change in the referenced table.

In the above example, if you input 47850 into C9, C15 will be 40 and cell J3 should change to 4. The change actually happens at 47855.

 

[StephenCrump]

C15 is actually 39.995..., not 40.

If you want to work to 2 decimal places, you should make:

C15: =ROUND(C17/C6,2)

 

[pommesmitmayo]

C15 is actually 39.995..., not 40.

If you want to work to 2 decimal places, you should make:

C15: =ROUND(C17/C6,2)

Sorry about the delay, have been so busy but you sir, are a gentleman.
That was it.

Thankyou very much.