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