Data that looks like a cell reference but isn't - how do I make Excel see it as a string.

DoubleBitAxe

New Member
Joined
Dec 12, 2016
Messages
2
I have data in a table that looks like a cell reference (namely, they are part numbers of the form N######) with parameters about each part. I am trying to use VLOOKUP to find the part of interest and extract particular parameters, but for numbers below 200000, for example N123456 excel sees it as a cell reference and looks for the value in cell N123456, which is typically a blank cell.

Is there a way to tell excel that I want it to use the value in the cell, not treat it as a reference? Sort of like the opposite of the INDIRECT function?

Thank you in advance.
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Welcome to the forum.

Unless you are typing N123456 directly into the formula, Excel will not see it as a cell reference. If that text is in say cell A1 and your VLOOKUP formula looks like:
=VLOOKUP(A1,....)
the formula is looking for N123456 and not the contents of the cell N123456.
 
Upvote 0
Can you explain a bit more? What exactly is the formula you are using when Excel sees it as a cell reference? I can't make it happen here.
 
Upvote 0
Thank you for taking the time to reply. That's how I expected it to work as well, but it was behaving unexpectedly.

It appears to be a bug resulting from running Oracle Crystal Ball, once I closed it the lookup worked as expected.
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,040
Members
448,543
Latest member
MartinLarkin

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