MrExcel Publishing
Your One Stop for Excel Tips & Solutions

VLOOKUP within VLOOKUP


Posted by John Beaudoin on February 06, 2002 11:42 AM

I have data containing shipping information including weight, carrier type and destination zip code. Each Carrier has a table defining Zones by destination zip code. Each carrier also has unique tables of weight by zone with shipping pricing within the table. I named each table (data range). I also created a table key by carrier for the zone and rate table names. With VLOOKUP and the Carrier Name, I can return the name of the Table I need to find the zone, however, without actually typing the name of the table in the formula, It will not go to the next VLOOKUP table to pull the zone. Is there some way to write a formula to do this that can be copy and pasted as there are some 43,000 records to perform this function on in one month of shipping data?

I tried:
=VLOOKUP(LEFT(N1,3),VLOOKUP(A1,TableKey,2,FALSE),2,TRUE)
N1 = Zip Code where First 3 Digits are on Zone Tables
A1 = Carrier Type, like FEDEX P1 or UPS BLUE
The value of VLOOKUP(A1,TableKey,2,False) might be equal to UPSZone or FEDEXZone, which are zone charts for the various carriers. This formula is not working, however, and I need to know if there is any kind of formula that could work for what I am trying to do.


Posted by Aladin Akyurek on February 06, 2002 11:49 AM

You are close. Try:

=VLOOKUP(LEFT(N1,3),INDIRECT(VLOOKUP(A1,TableKey,2,0)),2)

BTW: 0 means FALSE & you can just drop TRUE.

===========

Posted by John Beaudoin on February 07, 2002 6:53 AM

THANK YOU!!!