Conditional Lookup (IF/ISNA/Vlookup etc)

denhamch

New Member
Joined
May 1, 2012
Messages
4
Hi, first of thank you for all of your help I have received in the past by looking at your responses to others' posts. This, however, will be my first so please be kind. I have seen other posts related to this question but none for exactly what I am looking for. Here goes:

I am looking for a formula that will check a "Shipping Method" for its corresponding "Transit Time" on two seperate tables. This would be easy if every shipping method corresponded to a transit time, but FedEx and UPS Ground have different Transit Times depending on your location vs the destination. To be more specific:

I have a report that generates a shipping method for every order in column C, which I wish to use as a lookup value for the first table (below).
[e.g. C8=UPS GROUND]

If the lookup is not able to find it (which means it is UPS GROUND or FEDEX GROUND) I want it to lookup the State of the order which is in Column K against lookup Table 2 (below)
[i.e. K8=NV]

--------------------------------------------------

This is the first lookup table, to be used to calculate Transit Time (days) for all shipping methods except UPS GROUND and FEDEX GROUND
I have purposely left UPS GROUND and FEDEX Ground of this first table in case the ISNA function will need to be utilized [not sure about this].

Note:the text is one column and the value a second. (e.g. FEDEX 2 Day =S10, 2 =T10).
Note:The "CALC" which appears where a value normally does is a place saver, until I can come up with a table that lists every country and our transit time via Intl service there)


Column S | Column T
Row 8 FEDEX 1DAY FRT 1
Row 9 FEDEX 1ST OVERN 1
Row 10 FEDEX 2 DAY 2
Row 11 FEDEX 2 DAY FRT 2
Row 12 FEDEX 3 DAY FRT 3
Row 13 FEDEX EXP SAVER 3
Row 14 FEDEX INTL ECON CALC
Row 15 FEDEX INTL P1 CALC
Row 16 FEDEX LTL FRT CALC
Row 17 FEDEX P1 1
Row 18 FEDEX STD 1
Row 19 LTL/TL CALC
Row 20 UPS 2DA:AM 2
Row 21 UPS 2ND DAY AIR 2
Row 22 UPS 3DAY SELECT 3
Row 23 UPS INTL EXPED CALC
Row 24 UPS INTL SAVER CALC
Row 25 UPS LTL FREIGHT CALC
Row 26 UPS NDA:AM 1
Row 27 UPS NDA:SAVER 1
Row 28 UPS NEXTDAY AIR 1
Row 29 CUSTOMER P/U CALC

-------------------------------

The second table is to calculate Transit Time (days) for UPS GROUND or FEDEX GROUND Shipments.

Column V Column W
Row 8 CA 1
Row 9 NV 2
Row 10 UT 2
Row 11 AZ 2
Row 12 WA 3
Row 13 OK 3
Row 14 ID 3
Row 15 MT 3
Row 16 WY 3
Row 17 CO 3
Row 18 NM 3
Row 19 OK 3
Row 20 TX 3
Row 21 ND 4
Row 22 SD 4
Row 23 NE 4
Row 24 KS 4
Row 25 MN 4
Row 26 IA 4
Row 27 MO 4
Row 28 AR 4
Row 29 LA 4
Row 30 WI 4
Row 31 IL 4
Row 32 MI 4
Row 33 IN 4
Row 34 OH 4
Row 35 KY 4
Row 36 TN 4
Row 37 MS 4
Row 38 AL 4
Row 39 GA 4
Row 40 FL 4
Row 41 PA 4
Row 42 WV 5
Row 43 VA 4
Row 44 NC 4
Row 45 SC 4
Row 46 NY 5
Row 47 VT 5
Row 48 NH 5
Row 49 ME 5
Row 50 MA 4
Row 51 RI 4
Row 52 CT 5
Row 53 NJ 4
Row 54 DE 4
Row 55 MD 4

------------------------------------

This was the best I was able to do...

=IF(ISNA(VLOOKUP(C8,$S$8:$T$29,2,FALSE)),
VLOOKUP(K8,$V$8:$W$55,2,FALSE))

I thought I had solved it but when I extended the formula down to cells which were not ground it returned "FALSE." I want it to return a value from either Table 1 or Table 2 depending on if it is UPS GROUND / FEDEX GROUND (from Table 2) or whether it is another method (Table 1).


Thank you so much for your help,

Christian
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
denhamch,

If you're only using the state table if C8 contains the word ground, this should work:
=IF(C8="","",IF(COUNTIF(C8,"*ground*"),VLOOKUP(K8,$V$8:$W$55,2,0),VLOOKUP(C8,$S$8:$T$29,2,0)))
 
Upvote 0
Thank you!!!! That's it exactly, thanks for the lightning fast response!

Much appreciated

Christian
 
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,064
Members
448,545
Latest member
kj9

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
Back
Top