Rate Table Lookup. IF Statements or VLookup too difficult.

**ChaidaJones** · 04-26-2017, 03:01 PM

Hello,

I have the following issue and hopefully someone can help. I have a table for rating shipments based on mileage and weight. I need to see if there is an easier way to do this rather than using IF statements and VLOOKUPs.

Example: I need to rate the following two shipments -

Shipment 001 has a distance from origin to destination of 65 miles and a weight of 1250 lbs
Shipment 002 has a distance from origin to destination of 175 miles and a weight of 600 lbs

I need to pull the appropriate rate per lb from the from the following data set:

Capture.jpg

Thanks for your help!

**6StringJazzer** · 04-26-2017, 03:52 PM

You will have a difficult time creating any solution to this they way your data is provided. I would suggest that in the rate columns you put a number representing the maximum weight for that rate, so it is a number. Currently you have strings, which are a lot of work to break out the numbers. So for example your columns starting in D would be labelled 499, 999, 1999, 4999, etc.

If you do that, I have an idea of what the formula would be but I would rather test on your file first. Can you attach your actual file?

I don't know what you find difficult about IF and VLOOKUP; this solution will involve using INDEX and MATCH, probably not any less difficult.

**ChaidaJones** · 04-26-2017, 05:15 PM

Thanks for your help. I edited the strings like you mentioned. Please find the attached file. Thanks again.

**6StringJazzer** · 05-02-2017, 07:27 PM

I made another change to your rate sheet.

This solution doesn't take into account your minimum and maximum per shipment. I wasn't completely sure how that worked.

**ChaidaJones** · 05-24-2017, 03:34 PM

Thank you very much 6StringJazzer. This worked flawlessly. I really appreciate your help.

Rate Table Lookup. IF Statements or VLookup too difficult.

LinkBack

Thread Tools

Rate This Thread

Display

Rate Table Lookup. IF Statements or VLookup too difficult.

Re: Rate Table Lookup. IF Statements or VLookup too difficult.

Re: Rate Table Lookup. IF Statements or VLookup too difficult.

Re: Rate Table Lookup. IF Statements or VLookup too difficult.

Re: Rate Table Lookup. IF Statements or VLookup too difficult.

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] World Cup Predictions Table - LOOKUP and IF statements

[SOLVED] vlookup on an html table; difficult

[SOLVED] How to lookup tax rate from a reference table?

Change hourly rate wihout affecting previous table entries VLOOKUP

Difficult input table... complexed Vlookup function needed

VLookup/IF Statements and Multiple Variable Lookup Not Working Correctly

[SOLVED] Difficult Two Column Lookup

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions