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

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
 
F

Frank Kabel

Hi Rob
try
=SUMPRODUCT(('sheet2'!A2:A45=B5)*('sheet2'!B2:B45>=B4)*('sheet2'!C2:C45
<=B4),'sheet2'!D2:D45)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help with a really big IF statement 9
VLOOKUP OR MATCH FORMULA 12
If statement with Vlookup - including ISNA function to compare two columns from different worksheets 4
Match & Index function - Cell format problem that is keeping it fromworking for me 5
Run-Time error '-2147417848 (80010108)': Automation error The object invoked has disconnected from 0
Run-Time error '-2147417848 (80010108)': Automation error The object invoked has disconnected from 0

Top