Conditional Lookup (IF/ISNA/Vlookup etc)

[denhamch]

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

 

[tigeravatar]

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)))

 

[denhamch]

Thank you!!!! That's it exactly, thanks for the lightning fast response!

Much appreciated

Christian