# If Vlookup - different column data return

#### edgrimly

##### New Member
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
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
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
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
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

##### MrExcel MVP
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
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.

Replies
4
Views
386
Replies
9
Views
1K
Replies
1
Views
205
Replies
7
Views
200
Replies
1
Views
565

### Forum statistics

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.

### Which adblocker are you using?

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

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