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.]

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

Change the datetype of the leftmost column of your lookup table to text. This can be accomplished by using the Text to Columns... menu command and assigning the Text format at step 3 of 3.

 

[Yepper]

Mark and Seti -

A big thanks to the two of you! Both of the solutions you two proposed worked just fine. (Mark's method has the slight advantage in that it will also work with lookup values that are alpha-numeric.)

I guess one of the lessons I learned here is that re-formating a cell as text or number is not the same as converting them to text or numbers using the methodology you two described.

I would never have uncovered your solutions sifting through all the Excel manuals out there.

Thanks again!