Pivot and vlookup

[Susanne2020]

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

[#2]

@Susanne2020 

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.

[#3]

@Susanne2020 

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.

[#4]

@mathetesand @Sergei Baklan - 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 @Sergei Baklan'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

[#5]

@Sergei Baklan @mathetes 

[#6]

@Susanne2020 

I didn't find exact reason, but if re-build PivotTable it works. Most probably something with caches. Please check attached.

[#7]

@Sergei Baklan 

Hello again, 

I have now rebuild the pivot, but I can still enter a non-existing number in the filter and then I get this warning/question "No item of this name exists in the Pivottable report. Rename '2001811' to '8888888'?". See attached.

How do I prevent this ?

In your example I can only enter existing number, which is what I want.

[#8]

@Susanne2020 

My guess an error appears if you work with macros, but that's not my territory. If not, could you please share how to reproduce it on the workbook without macros.

[#9]

@Susanne2020 

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)

[#10]

@Sergei Baklan 

Yes I work a lot with macros/VBA and that can give some 'fun'- most of the time it works perfectly.

@Charla74 

Perfect ! I have changed it and it works without problems - thank you