Vlookup #N/A Problem

[jschoon4]

I am trying to pull Item pricing from Sheet 2 over to Sheet 1. However, It does not recongize/pull pricing for every item. For the items on Sheet 1 that Vlookup can't find from Sheet 2 it returns an #N/A which is Fine I can fix that. BUT, If I go to Sheet 2 and Ctrl-F (control Find) enter that item number It does find the item.
Sheet 1 only has a Mfg. # and Sheet 2 has both Mfg.# and Pricing

Q) So why isn't Vlookup finding that item number on Sheet 2 and return the Price for me on Sheet 1.
Funtion I am using is
=IF(ISNA(VLOOKUP(K3,Sheet3!$A$2:$B$5551,2,0)),"0",VLOOKUP(K3,Sheet3!$A$2:$B$5551,2,0))

 

[mvptomlinson]

Make sure that your lookup value and find value are the same type, e.g. don't try searching for the text string "123" in a list of numbers, and vice-versa (looking up a number against a list of text strings).

Also, in Excel 2007+ you can us IFERROR instead of IF(ISNA or IF(ISERROR.

=IFERROR(VLOOKUP(K3,Sheet3!$A$2:$B$5551,2,0),0)

 

[Jonmo1]

Well for 1, you say Sheet 2, but your formula reads Sheet 3....
I'm sure that's just symantecs though...

I am trying to pull Item pricing from Sheet 2 over to Sheet 1. However, It does not recongize/pull pricing for every item. For the items on Sheet 1 that Vlookup can't find from Sheet 2 it returns an #N/A which is Fine I can fix that. BUT, If I go to Sheet 2 and Ctrl-F (control Find) enter that item number It does find the item.
Sheet 1 only has a Mfg. # and Sheet 2 has both Mfg.# and Pricing

Q) So why isn't Vlookup finding that item number on Sheet 2 and return the Price for me on Sheet 1.
Funtion I am using is
=IF(ISNA(VLOOKUP(K3,Sheet3!$A$2:$B$5551,2,0)),"0",VLOOKUP(K3,Sheet3!$A$2:$B$5551,2,0))

Perhaps your numbers are not really numbers, but "numbers stored as text"..

On Sheet 2 (or 3)
Copy any blank cell
Highlight column A
Right click - paste special - values - add - OK

 

[taurean]

Sometimes Texts carry invisible characters such as "space" so the VLOOKUP doesn't work. Can you confirm if this is not the case?

 

[jschoon4]

I noticed a green litle trianlge in the corner. It said in the yellow pop up box next to the cell that the numbers are formatted as a TEXT. Thanks everyone I changed the format to a Number! I'll also try using IfError Look easier.