VLOOKUP problem #NA error

[I2omani]

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.

 

[ajm]

you got me? I have no idea. I will go with the Trim version for now. many thanks to everyone and especially Aladin.

 

[Aladin Akyurek]

you got me? I have no idea. I will go with the Trim version for now. many thanks to everyone and especially Aladin.

That is a too expensive solution...

Does the following (no control+shift+enter)...

=VLOOKUP("*"&D3&"*",Sheet3!A:D,2,0)

yield the expected results?

 

[ajm]

no luck. care to take a look at the actual file to see if i have overlooked anything?

 

[I2omani]

now here is the fun part " i knew it will not go smooth all the way"

VLOOKUP(D3,Sheet3!A:D,2,FALSE)

what if i have 2 of the same value in D3 and 2 diffrent values in Sheet3!A:D,2 how can i do that?
e.g D3 value
004
005
004

and for the 1st 004 i have S/n 0123456, and 65487952 for the 2nd one!

any suggestion on what to do?
thanks in advance

 

[Aladin Akyurek]

now here is the fun part " i knew it will not go smooth all the way"

VLOOKUP(D3,Sheet3!A:D,2,FALSE)

what if i have 2 of the same value in D3 and 2 diffrent values in Sheet3!A:D,2 how can i do that?
e.g D3 value
004
005
004

and for the 1st 004 i have S/n 0123456, and 65487952 for the 2nd one!

any suggestion on what to do?
thanks in advance

See:

http://www.mrexcel.com/forum/showthread.php?t=299896

for an approach imvolving this type of questions.

 

[starlady]

I get an #NA error with this.

=IF(VLOOKUP(A4,INTVL,4,0)=0,VLOOKUP(A4,INTVL,5,0),-VLOOKUP(A4,INTVL,4,0))

anyone pls help??

 

[shg]

It means there was no exact match for the value in A4 in the first column of range INTVL.

 

[shg]

If the formula otherwise worked, and you're using Excel 2007+, it could be simplified to

=IFERROR(1/(1/VLOOKUP(A4,INTVL,4,0)), VLOOKUP(A4,INTVL,5,0))

 

[Aladin Akyurek]

I get an #NA error with this.

=IF(VLOOKUP(A4,INTVL,4,0)=0,VLOOKUP(A4,INTVL,5,0),-VLOOKUP(A4,INTVL,4,0))

anyone pls help??

If a numeric (non-blank) result is expected, try...

=LOOKUP(9.99999999999999E+307,CHOOSE({1,2,3},0,
    VLOOKUP(A4,INTVL,5,0),-VLOOKUP(A4,INTVL,4,0)))

 

[sanjayplayer]

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.

=======
#Na is usually shows due to mismatch of values. means either the values is not matching or not avaible.