Vlookup with Pivot table

rentonhighlands

Board Regular
Joined
Jul 31, 2014
Messages
134
Office Version
  1. 365
Platform
  1. Windows
Hello,
I want to use vlookup function. I want to lookup using data in a pivot table against data that is not in a pivot table. I tried and get #N/A error. Why?
I checked I see data in pivot table is the same data in my other sheet.
Is it that the formatting in the pivot table does not allow for vlookup to work?
 
VLOOKUP works fine in looking up values in a Pivot Table - see below.

If you cannot get it to work then it might be best to show us a small set of sample data, the resulting Pivot Table and a VLOOKUP formula or two (with the incorrect results) with XL2BB, similar to what I have done.
(If you have trouble with XL2BB, review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of the XL2BB Instructions page linked above.)

25 02 24.xlsm
ABCDEFGHIJKLM
1NameMonthAmountSum of AmountMonthNameAmount
2aFeb1NameJanFebGrand Totalb14
3cJan4a347a7
4aJan3b8614c13
5bJan2c4913
6bJan5Grand Total151934
7bFeb6
8cFeb7
9aFeb2
10aFeb1
11bJan1
12cFeb2
Sample
Cell Formulas
RangeFormula
M2:M4M2=VLOOKUP(L2,F$3:I$10,4,0)
 
Upvote 0
VLookup works to look up from data in pivot table to my other sheet for some lines but not for others. Can it be the way data if formatted in pivot table vs the format of data in my other sheet?
How can I check to see if the format is the same in both data sets?
 
Upvote 0
Can it be the way data if formatted in pivot table vs the format of data in my other sheet?
Yes it can.


How can I check to see if the format is the same in both data sets?
It might depend on just what the data is like. The most common problem in this sort of situation is with data that is (or looks) numerical and it turns out one set of data is numerical and the other is text that looks like numbers.
To test that use the formula =ISNUMBER(xx) where xx is the cell address for one of the "numbers' in the pivot table and then do the same thing where xx is the cell address for one of the "numbers" in the other sheet.

If your issue is not related to numbers or text that looks like numbers, then review the section above the mini sheet in post #2 and also give us a small sample (also with XL2BB) from the other sheet.
 
Upvote 0

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