Strange Excel Quirk?

Spencer25

Board Regular
Joined
May 3, 2006
Messages
76
Hi all, when I do a vlookup, the value returned is "#ref" - even though the cells from both sheets are formatted the same. If I put an apostrophe in front of the number being looked up - nothing happens. However, when I remove the apostrophe (back to the way it was) the cell returns the proper value. Any ideas?
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Dufus

Board Regular
Joined
Aug 19, 2006
Messages
176
Spencer25,

If you have taken data from another source and added it to your sheet to be used in a lookup, you can get this behavior. It's caused by the laying on of data on top of a format. The data doesn't necessarily have the same format as the cell underneath. What happens when you change from text (adding ' ) and then back is what needs to happen to all the data. That is hit F2 and then Enter. That gets the data into the cell in the format of the underlying cell. To do this for a lot of data, you want a shortcut. You want to copy a blank cell and Paste Special, Add to the entire range of data. This will add zero to all the cells in much the same way as hitting F2 and then Enter.

Dufus
 

Forum statistics

Threads
1,141,720
Messages
5,708,078
Members
421,545
Latest member
TWR

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
Top