Vlook up problem with pivot tables

[Joe Martino]

I am using excel 2010.

Question:

What Am I doing wrong with my syntax?
Or is the VLOOKUP formula not the one I should be using as it doesn't work with a pivot table?


Background:
I have a multiple tab work book that contain external data sources. Specifically QQube (data warehouse that pulls from Quickbooks)

([@[SalesTxn Shipping Address Postal Code]] is the external data reference.
Distances! is another tab on the workbook that I am trying to pull in a dollar amount.

I am trying to pull the zip code from ([@[SalesTxn Shipping Address Postal Code]]
, then use Vlook up to reference the Distances tab for a travel rate.

I have verified that my table is sorted from smallest to larget in column 1 and my row column selection is column 2 as you can see from my formula.


My formula:

=VLOOKUP([@[SalesTxn Shipping Address Postal Code]],Distances!A24:B924,2)

Answer is #N/A

I have the correct syntax using the formula wizzard. I think it is a problem with the pivot table or calculated fields. I am pushing the edge of my knowledge and need some help on what I am doing wrong.

Any help would be appreciated.

Joe

 

[JAlbers]

Joe
Are your postal codes formatted as text or numerical? I finally gave up on using vlookup and switched over to index(match()) as it seems to be a bit less particular about your data formats, (or I just got used to doing it the way it wanted.) At any ate that is my experience for what it's worth.

Jake