If Vlookup - different column data return

[edgrimly]

Ok, so the subject probably is not descriptive enough but hey, I don't really know what to call it. I DO bet strongly that this will be an easy fix.

So I have a spreadsheet where I have a list of order IDs, and shipping prices to be looked up per the order ID. My problem is that there are three columns of shipping costs on another worksheet (range "shippingcharges"). Only one column has the shipping charges and the other 2 will have 0 entered into it.

IF shippingcharges column 7 =0 THEN LOOKUP 8 and so forth through all three columns.

Below is what I have written to this point (for 2 columns) and it works as long as the shipping charges are on the first column. If the first column is 0 then I get nothing.
=IF(VLOOKUP(A9,shippingcharges,9,FALSE),0,VLOOKUP(A9,shippingcharges,8,FALSE))

It appears as if it is not doing the IF and just doing the VLOOKUP.

If I have not explained it clearly, I will try one other way.

VLOOKUP checks A1 for OrderID and Vlookup's the OrderID in range "shippingcharges" (which has 9 columns, last 3 of which are costs).
IF the seventh column of shipping costs = 0 then return costs in column eight, If the costs =0 then return costs from the ninth column.

This has GOT to be an easy one but I am losing it and cannot see it.

 

[Jonmo1]

Only 1 of the 3 columns per row will have a number in it?

Try using MAX

=MAX(VLOOKUP1, VLOOKUP2, VLOOKUP3)

like this

=MAX(VLOOKUP(A9,shippingcharges,7,FALSE),VLOOKUP(A9,shippingcharges,8,FALSE),VLOOKUP(A9,shippingcharges,9,FALSE))

 

[Derek Brown]

Try something like

=IF(VLOOKUP(A9,shippingcharges,9,FALSE)<>0,VLOOKUP(A9,shippingcharges,9,FALSE),
IF(VLOOKUP(A9,shippingcharges,8,FALSE)<>0,VLOOKUP(A9,shippingcharges,8,FALSE),
VLOOKUP(A9,shippingcharges,7,FALSE)))

Of course the above code assumes that a match against A9 will always be successful - it is also worthwhile checking out the ISNA function which would be used with the VLOOKUP function to check for failure to find a matching value (and therefore returning "#N/A").

 

[barry houdini]

Only one column has the shipping charges and the other 2 will have 0 entered into it..

..so you can sum all 3 columns...try

=SUMPRODUCT(VLOOKUP(A9,shippingcharges,{7,8,9},0))

 

[edgrimly]

SWEEEEEEEEEETTT
I LOVE learning new Excel functions. Of course MAX is old to everyone but ME!!! But hey, it works. It actually does better than what I was trying to do and simpler also. The jonmo1 MAX code worked perfect straight out of the box.

I also tried the d3p2j5b Vlookup and it works great as well. Looks like I was on the Vlookup track but gave up halfway. Got the If but not the THEN.

Thanks for making this a headslapping slam dunk.

 

[barry houdini]

You could also use a similar formula to my suggestion above but with MAX as per jonmo's suggestion, i.e.

=MAX(VLOOKUP(A9,shippingcharges,{7,8,9},0))

but this needs to be confirmed with CTRL+SHIFT+ENTER

 

[edgrimly]

Wow.. the formulas keep getting smaller. I was wondering when you would use an array. I have SOOO much to learn about Excel. Really puts you in your place when you start to see what all this program can do. Scary thing is I am considered the "expert" in Excel in our facility, heheh I just know who to ask Thanks again.