VLOOKUP problem #NA error

I2omani

Board Regular
Joined
Feb 8, 2007
Messages
85
This is a weird one I am trying to wrap my head around.

I created a workbook with three sheets, I do a vlookup formula that looks like this:=VLOOKUP(D3,Sheet3!A:D,2,FALSE)

so basically, find the value of D3 and look for the exact match in sheet 3 (column range a-d) then report back the value found in the second column.

I get an #NA error with this. Funny thing is that if I go to sheet 3, find the correct value and "re-type" it in, it will now pull the information I want.

I've tried some basic formatting changes that dont fix the issue and the only thing that seems to work is retyping the values into sheet 3.

any ideas?

I've got about 1500 rows I'd have to retype so the idea doesnt excite me.
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
The obvious thing to check is whether you have leading or trailing spaces in the values in Sheet3.
 
Upvote 0
The #N/A is being returned because it is not finding an exact match, but when you retyped it does, this would tend to indicate that what you retyped in the cell is not exactly what was there before.

Perhaps there are extraneous spaces or non-breaking spaces in the values in the lookup table
 
Upvote 0
If it's just a formatting issue, you can usually apply the appropriate format to that range and goto Data-->Text to Columns-->Finish to apply it.

HTH,

Smitty
 
Upvote 0
You could try something like this instead

=VLOOKUP(D3,TRIM(Sheet3!A:D),2,FALSE) confirmed with Ctrl+Shift+Enter

Seemed to do the job for me, to my surprise. :)

KR


Dave
 
Upvote 0
folks, the question is, why does dave's method work and not the others? I am having similar issues and have tried each of the "normal" fixes with no luck. doing an array formula down the entire page is going to take sometime. strangely enough, dave's formula stops working on my sheet if you manually type in the lookup value.
 
Upvote 0
folks, the question is, why does dave's method work and not the others? I am having similar issues and have tried each of the "normal" fixes with no luck. doing an array formula down the entire page is going to take sometime. strangely enough, dave's formula stops working on my sheet if you manually type in the lookup value.

I don't think you have tried ASAP Utilities to clean up stray spaces that apparently exists in your table area.
 
Upvote 0
Aladin,

I started over and used ASAP Utilities to cleanse both the lookup values and the table array of leading and trailing spaces. When that didn't work, I tried the "Leading, Trailing, and Excessive" spaces option. Nada.
 
Upvote 0
Aladin,

I started over and used ASAP Utilities to cleanse both the lookup values and the table array of leading and trailing spaces. When that didn't work, I tried the "Leading, Trailing, and Excessive" spaces option. Nada.

If TRIM works, the options you tried will work too.
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,034
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