Forum Discussion
Pivot and vlookup
Hello,
I have made a pivot table, where I use a field in the filter to lookup another informations. For example I use the customer number as a filter in the pivot - but I also use it to 'vlookup' the customer name, address, bonus.
But now I have by a mistake entered a wrong customer number, that doesn't exists and I accidently pulled 'Ok' to rename the data. I renamed it back - but now my lookups don't work anymore for that particular customer no.
I have tried several things several times :
Checked the spelling and no blancks - removed the field - refreshed the pivot - recalled the 'raw data'
- checked the vlookup. Nothing helps.
It works perfectly for all the other customer, but not this one.
I hope you can help me.
B.R. Susanne
I
9 Replies
Change your formula in D2 to include INT(B2) rather than just B2 - this looks for the whole number and discounts any trailing decimals which can throw off a formula looking for an exact match:
=VLOOKUP(INT(B2),Customer!A:B,2,FALSE)
Yes I work a lot with macros/VBA and that can give some 'fun'- most of the time it works perfectly.
Perfect ! I have changed it and it works without problems - thank you 🙂
Try simply to compare value for changed number with one in lookup array, like =Cell1=Cell2
Perhaps data type was changed from number to text (check the cell by =ISTEXT()). Or non-printable character was added (check by =LEN() ). Or something else. As mathetes mentioned without sample file it's hard to say something concrete.
It sounds like you've done a reasonable search for possible causes. Is it possible for you to post a copy of the spreadsheet(s), just making sure first to remove any actual names, addresses, etc.--i.e., any and all confidential or personal info.
mathetesand SergeiBaklan - thank you for your answers.
Now I have made a light version of the spreadsheet :
On sheet 'Pr. kunde' - in field 'CustAddrNo' :
If I use no. 2001792 everything is fine.
If I use no. 2001811 my lookups are interrupted.
I have tried SergeiBaklan's suggestions and it seems to be text now.
How did that happened ? (I know it happened when I by a mistake overwrite the number and then changed it back - but why did it then became text ?)
How do I correct it ?
And how do I prevent other users to do the same failure ?
Hope you can help 🙂
