Vlookup or any other function

[capricorn123]

Mr (or Ms) Excel Community,

I need your help to figure out the formula as I hit the roadblock.

I used the following formula to come up with the reporting liters value of the below table:
=IF(AND(B4>=D2,B4<=E2),C2)*B3

The outcome I am trying to achieve that we manually input gallons value (B2) and Brix value (B4). It will lookup column D & E and if Brix value falls between any of those ranges from D2 to E13, it will relatively pick column C value and times it by Liters (B3) and give us the reporting liters value.

e.g. If I enter brix value (B4) 41.7 it will automatically pick 3.5 (row6) and times it with Liters (B3) so we can get Reporting Liters value.

PS: any Brix value less than 19.46 should pick "1" and any value greater than 70.07 should pick "C13".

[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 481"]
<tbody>[TR]
[TD]1[/TD]
[TD] A[/TD]
[TD] B[/TD]
[TD] C[/TD]
[TD] D[/TD]
[TD] E[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Gallons[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]1.5[/TD]
[TD="align: right"]19.46[/TD]
[TD="align: right"]22.42[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Liters[/TD]
[TD="align: right"]378.54[/TD]
[TD="align: right"]2.0[/TD]
[TD="align: right"]22.43[/TD]
[TD="align: right"]28.15[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Brix[/TD]
[TD="align: right"]19.9[/TD]
[TD="align: right"]2.5[/TD]
[TD="align: right"]28.16[/TD]
[TD="align: right"]33.61[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3.0[/TD]
[TD="align: right"]33.62[/TD]
[TD="align: right"]38.83[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3.5[/TD]
[TD="align: right"]38.84[/TD]
[TD="align: right"]43.84[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Reporting Liters[/TD]
[TD] 567.81[/TD]
[TD="align: right"]4.0[/TD]
[TD="align: right"]43.85[/TD]
[TD="align: right"]48.65[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD="align: right"]4.5[/TD]
[TD="align: right"]48.66[/TD]
[TD="align: right"]53.26[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]5.0[/TD]
[TD="align: right"]53.27[/TD]
[TD="align: right"]57.69[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]5.5[/TD]
[TD="align: right"]57.70[/TD]
[TD="align: right"]61.97[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]6.0[/TD]
[TD="align: right"]61.98[/TD]
[TD="align: right"]66.09[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]6.5[/TD]
[TD="align: right"]66.10[/TD]
[TD="align: right"]70.07[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]7.0[/TD]
[TD="align: right"]70.08[/TD]
[TD="align: right"]73.92[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Fingers Crossed and thank you for your help in advance!!

 

[Fluff]

Hi & welcome to MrExcel
How about
=IFERROR(INDEX(C2:C13,MATCH(B4,D2:D13,1)),1)*B3

 

[capricorn123]

Thank you so much Fluff!!! You are a Star!

 

[Fluff]

You're welcome & thanks for the feedback