vlookup, IF or Match? or is a custom function required?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have 4 columns of freight rate information that I am trying to analyze with a function to pull back the rate for a shipment based on Region and Weight

I have named ranges as follows on Sheet 2

Range A2:A45 = "Region"
Range B2:B45 = "MinWeight
Range C2:C45 = "MaxWeight
Range D2:D45 = "Rate

Example values are: Region = 6, MinWeight = 501, MaxWeight = 1000, Rate = $18.0

On Sheet 1 I enter Weight = 704 in cell B
On Sheet 1 I enter Region = 6 in cell B
I am trying to pull the value "$18.00 to cell B6 by calculating

If B5 = Region AND Weight >= MinWeight AND <= MaxWeight Then Rat

The preceding line is not correct, but it represents what I am trying to do. Does anyone have advice on this

Thank you

Ro
Bellevue, W
Microsoft Certified Professional
 
Hi Rob
try
=SUMPRODUCT(('sheet2'!A2:A45=B5)*('sheet2'!B2:B45>=B4)*('sheet2'!C2:C45
<=B4),'sheet2'!D2:D45)
 
Back
Top