VLOOKUP #N/A Error that's new to me Excel 2010

SuperLute58

New Member
Joined
Jan 23, 2014
Messages
6
So I have the formula written exactly how it's worked many other times.

=VLOOKUP(B3, Sheet2!A:B, 2, FALSE)

I have found the value of B3 on Sheet2. I know it exists. I found it using the find feature. I have Sheet 2 sorted. Both Column B on my formula page and the Sheet2 column are are formatted the same. My table array is correctly aligned. However it's pulling back #N/A.

Please save the life of my computer so I don't have to cause it harm.
 
does =len() return a value you expect for these 'numbers' - might be a hidden space? How are these values being generated - some details would help.

Managed to get it solved. len revealed 8 characters instead of the 7 visible. I then went with =Value(right(B3, 7)) to remove the mysterious front character since it wasn't a space to delete. The new value matched up on len with 7 char. From there I pasted values only over the original number and all the formulas worked.

Thanks a million everyone!
 
Upvote 0
The basic problem is that, for whatever reason, the two things you are trying to match are not the same. So I'm afraid you need to get into that mind set first.
You say you have a lot of data, no problem, just use the value function and make a helper column with =Value(your data) where your data is a relative reference to the offending text typed numbers. Then just copy the resultant column and paste special/values over the original data.

Oops, I was too slow, you did exactly that! :)
 
Upvote 0

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