Calculated Field - vLookup

[KVJS]

Getting error message "References, names and arrays are not supported i
pivot table formulas"
When entering vLookup in a pivot table calculated field.
Example:
Name: TblLu
Formula: =vlookup(Re_Num,newrates,34,false)

Please help.
Thanks,
Ke

 

[Spencer101]

Getting error message "References, names and arrays are not supported i
pivot table formulas"
When entering vLookup in a pivot table calculated field.
Example:
Name: TblLu
Formula: =vlookup(Re_Num,newrates,34,false)

Please help.
Thanks,
Ken

Hi Ken,

Is "Re_Num" in your workbook a single cell or a range of cells

 

[KVJS]

Hi Ken,

Is "Re_Num" in your workbook a single cell or a range of cells?

Hi Spencer,
A single cell.
All I am trying to do using an Item Id is read a table and display inf
from that table. Re_Num is simply the item Id.
Thanks,
Ke

 

[Ben McClave]

Ken,

It is my understanding that PivotTable Calculated Fields cannot use any formula that requires a range as one of its arguments. There are a few ways to work around it. Here are a couple of suggestions:

1. Use the CHOOSE function. If you have 29 or fewer lookup items, you could use the CHOOSE function to select the appropriate item from the list.

2. Add a column to your source data. If you are able, simply add a column to the table that feeds your PivotTable and use the VLOOKUP within that column instead. Then, that new column will be available to add as a field to your PivotTable.

3. Use the GetPivotData function. To use this option, simply use the VLOOKUP in a range of cells outside of the PivotTable and use GetPivotData to pull in any data needed from the PT.

None of these may be ideal for your needs, but hopefully one of them will work out.

Ben