How to change Excel Pivot table column to text format for vlookup purposes

0

1

I have a column in my data that is ID numbers, basically. Made a pivot table out of the data. I'm trying to do a vlookup into the pivot that ignores the first number in the data like so:

=vlookup("*"&A1,pivot table columns,2,false)

But I get N/A because the *&A1 is considered text, which I cannot change, and the pivot table data is considered to be numbers. I've tried going to the original data and changing the formatting to text and refreshing, I've tried formatting the cells where the data appears and I've tried going into field options and changed the number formatting to text. None of it worked.

Anyone know an easy way to get this to work?

Matt

Posted 2014-06-03T17:55:16.683

Reputation: 13

Changing the format of a column doesn't always change the contents. You may have to change the table data type by using Text to Column and choosing the Text format. – CharlieRB – 2014-06-03T18:11:53.147

Answers

1

Force your ID number to be treated as text in the source data.

To do this reliably:

  1. select the column in your data source with ID number
  2. Data->Text-To-Columns
  3. Next
  4. Next
  5. Choose Text
  6. Finish
  7. Refresh the Pivot.

Madball73

Posted 2014-06-03T17:55:16.683

Reputation: 2 175

Thanks! That worked. I forget about that text to columns function. – Matt – 2014-06-03T18:33:42.107

Asked: 2014-06-03T17:55:16.683

Viewed: 8 051 times

Active: 2014-06-03T18:11:00.983