If Vlookup - different column data return

edgrimly

New Member
Joined
Jul 23, 2007
Messages
9
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.
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Only 1 of the 3 columns per row will have a number in it?

Try using MAX

=MAX(VLOOKUP1, VLOOKUP2, VLOOKUP3)

like this
Code:
=MAX(VLOOKUP(A9,shippingcharges,7,FALSE),VLOOKUP(A9,shippingcharges,8,FALSE),VLOOKUP(A9,shippingcharges,9,FALSE))
 

Derek Brown

Well-known Member
Joined
Dec 26, 2005
Messages
2,390
Try something like
Code:
=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

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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

New Member
Joined
Jul 23, 2007
Messages
9
SWEEEEEEEEEETTT :biggrin:
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. :rolleyes:
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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

New Member
Joined
Jul 23, 2007
Messages
9
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.
 

Forum statistics

Threads
1,181,717
Messages
5,931,651
Members
436,796
Latest member
parthibanmuniswamy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top