Reference two columns to get result in table array

[mrussell555]

Hi There,

I am trying to create a formula linking two worksheets together. One sheet has a shipping tariff, and another has the shipment information (pieces, weight, origin, destination). I have been trying to create the formula using video tutorials, but I think I may be missing a step. Here is the formula I have so far:

=MIN(MAX(HLOOKUP($H5,'JNN LH Rates'!$A$1:$M$1,MATCH($D5&$E5,INDEX('JNN LH Rates'!$A$2:$A$4057&'JNN LH Rates'!$B$2:$B$4057,),0)*$H5,VLOOKUP($D5,MATCH($D5&$E5,INDEX('JNN LH Rates'!$A$2:$A$4057&'JNN LH Rates'!$B$2:$B$4057,),3,0),VLOOKUP($E5,MATCH($D5&$E5,INDEX('JNN LH Rates'!$A$2:$A$4057&'JNN LH Rates'!$B$2:$B$4057,),13,0))))))

H5 represents the weight
D5 represents the origin
E5 represents the destination

on JNN LH Rates spreadsheet
Column A = origin
Column B = destination
Row 1 = weight break per pound being charged

Table information is per pound charges for weight, with minimum/maximum charges per origin/destination.

The end result is for the formula to calculate the charge per pound. Then determine if it meets the minimum charge, or if it is under the maximum charge, if it is, then the per pound charge applies, if not, then the minimum or maximum charge applies.

The error I get, is that I've entered too many arguments for this function. Is there an easier way to achieve what I need for my end result?

Thank you in advance for any help or advice you can give!

Megan

[p24leclerc]

It would help us a lot if you could attached a workbook with some data in it along with examples of desired results.

[mrussell555]

Sample data for Excel formula problem.xlsx

Here is a sample file. I was trying to break down the formula (unsuccessful so far). It wouldn't let me save it with the formula that had an error.

Any help is MUCH appreciated!

Thanks,

Megan

[p24leclerc]

First of all, there is no LAX B in your sheet JNN LH Rates. Is it OK for now because it's only a sample sheet?
Then, your formula goes into column AB. Right?
We have nothing to do with the other sheetJNN LAX Rates. Right?

[mrussell555]

Yes, sorry, I deleted a bunch of lines to make it easier for uploading (the original file had over 4,000 lines on JNN LH Rates). But it still wasn't working even with LAX B listed in the data. You are correct that JNN LAX Rates are part of this question. And yes, the formula should go in column AB row 5 (for this example).

Thanks!

[p24leclerc]

Do you mean JNN LAX Rates is part of the formula? If so, how is it used?
can the origin and dest be the same place. If so, how is it calculated?

[mrussell555]

Sample data for Excel formula problem.xlsx

Sorry, no, it is not part of this particular problem. (My hands were not typing what my brain was thinking)

I am re-uploading the file with an example of LAX B, and that worksheet gone. I can get part of the way through the formula just fine as you'll see on AB5, but I still need it to look at the minimum and maximum for the same shipping zone, compare them and select the appropriate one.

For the other file that was uploaded, you can see that on rows 2 & 3 for column AB, it is looking at JNN LAX rates and doing a similar function, only the tariff is set up differently AND it is only having to look at one shipping zone.

I think I had left that on initially just to show that it had worked in a different scenario.

Thanks,

Megan

[mrussell555]

Also, in these particular scenarios, the destination, will never be the same as the origin, that is calculated with the formula that I was using referencing the JNN LAX spreadsheet on row 2 & 3

[p24leclerc]

See attached file and formula in cell AB5.
Note that I used NAMED RANGE to ease formula. You can see those by clicking on the FORMULA tab and then on NAME MANAGER.
Hope this does what you wanted.