VLookup problem when used with Pivot Table

[Yepper]

Hello -

I am trying to create a simple VLookup table using Excel 2002. The lookup value (the first argument) is referencing a column from a pivot table located to its left. The table array (the second argument) is on a separate worksheet in the same workbook. I am also using "False" as the range lookup (the fourth argument) to get an exact match.

The problem: the value returned is an error message "#N/A" -- even though I know there is an exact match, at least one that looks like it to the naked eye. However, there must be something different about the lookup value that is not apparent, because when I type in the lookup value, instead of referencing its cell location from the pivot table, the correct value is returned.

Questions: (1) What is it about the "values" in a pivot table that make them different from a normal number? Whatever that is is preventing the lookup value in the pivot table from identifying an exact match in the table array. I've tried reformating the pivot table lookup values and the table array so that they both have the same format but still no luck. I have also tried copying the pertinent values in the pivot table to another column and then copied them again using paste-special value and then referenced those values -- but still no success either. The lookup seems to only work if I re-key the lookup value in the privot table. (2) Is there a workaround/solution?

Thanks for your help!

 

[Mark W.]

Don't use VLOOKUP to extract data from a PivotTable -- use GETPIVOTDATA instead.

 

[Yepper]

Mark -

Thanks for your suggestion. However, I don't believe this will work, because I am actually trying to get/return data from the table array -- not from the pivot table. In my situation, I have a pivot table with a column of selected Entity codes. On a table array on a separate worksheet (but same file), there is a master list of Entity codes along with the corresponding entity descriptions. I am trying to use the VLookup function to place the corresponding entity descriptions next to the entity codes that are shown on the pivot table.

I've never used the GetPivotData function before and am uncertain as to its capabilities. Can GetPivotData do what I am trying to accomplish as described above? If so, can you provide some guidance?

Thanks again...

 

[jj51574]

This exact thing happened to me last week. And although I don't know the reason, I had to make sure the array table came from a pivot table too, then that worked.

 

[MDuff]

Yepper

here is a good example on how to use GETPIVOTDATA
http://www.techonthenet.com/excel/pivottbls/getpivotdata1.htm

Hope it helps

Edit

PS you Might want to check your data Becuse I have ran a vlookup off a PivotTable in the past.

 

[Mark W.]

Mark -

Thanks for your suggestion. However, I don't believe this will work, because I am actually trying to get/return data from the table array -- not from the pivot table. In my situation, I have a pivot table with a column of selected Entity codes...

Provide some examples of your Entity codes.

 

[Yepper]

Thanks MDuff and jj51574.

jj51574, are you saying that you converted your table array to a pivot table, and then you were able to use the VLookup function as you had hoped to originally?

(jj51574, as a side note, I just tried that. But for some reason, when I attempted to convert my simple table array [a column for entity codes and another column for the corresponding entity descriptions] to a pivot table, the resulting pivot table would not show the descriptions. Instead, it just listed a "1" next to each entity code, as if to count the number of description for each code. What am I doing wrong here??!!)

 

[Mark W.]

Provide some examples of your Entity codes.

 

[Seti]

If you are using numbers as the column from the pivot table to vlookup into other data, my guess is that the pivot table numbers are really text. Try this:

VLOOKUP(VALUE(pivot table data),array,colnum,FALSE)

I have had this same problem many times.

 

[Yepper]

Mark -

The Entity codes consist of three characters -- either alpha or numeric or both. For example, 120 is a code. 26V is also a code.

Additional information: the VLookup does work for those Entity codes that are made up of both alpha and numeric characters. But the VLookup does not work for codes that are only numeric.

Thanks...