Using Vlookup in PivotTable Calculated field

mines

New Member
Joined
Oct 29, 2008
Messages
5
Hi there,

How can i use Vlookup in the Calculated field in Pivot table.

i am trying to get an information from a different sheet to do some calculation , but i get an error saying "references,Names,Arrays are not supported in pivottable formulas"


the formula i am using is =VLOOKUP(CONCATENATE( DocYear , DocMonth ),[DailySalesReport.xlsx]Cost!$A:$L,3,0) .

is there a work around for this.
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Hi. If I understand correctly, the solution is to join the tables in SQL. For example, if you name the source data tables (headers & data rows) with normal defined names and from a new workbook start the pivot table wizard taking the external data option at the first page. Follow the wizard to the end & take the option to edit in MS Query. Hit the 'SQL' button and edit the SQL, OK to enter it and then hit the 'open door' button and then complete the pivot table. The SQL must be specific to your field names, which I don't know, but maybe below gives you the idea? When finished the resultant worksheet containing the pivot table can be moved into the source data workbook if you like. HTH, Fazza

Code:
SELECT A.*, B.Sales
FROM main_table A, lookup_table B
WHERE A.DocYear & A.DocMonth = B.DateField
 
Upvote 0
Thanks fazza,

the issue is , i am not dealing with tables/sql.

i have two sheets , the first one have sales figure, the second one is the operating cost, depending on the month should be picked. i can do a workaround by applying the vlookup in the sheet one, and doing arithmetic operations there. but my problem is ,
Is there anyway i can use a vlookup or a likewise function in the pivot table calculation formula to pick a value based on some condition?

Thanks
 
Upvote 0

Forum statistics

Threads
1,213,556
Messages
6,114,284
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top