help me

nguyenthehoat · May 4th, 2002, 12:22 AM

I 'm using windows Xp and office XP.
working in ware house section I have to control about 3000 kind of material. I usually use vlookup formula, it is very convenient but unfortunately, sometime data format in sheets automatically change to different type so my formula can not work correctly."#N/A". If I copy vlookup value from table ray it will return correct value. I tried to format vlookup value and the value in table ray in the same type (text, general,number) but my formula still doesn't work. It will take some months to input this data again. Please help me fix this problem.
Many thanks in advance.
Mr.The Hoat
Yazaki Haiphong,Vietnam

rmtaylor · May 4th, 2002, 01:59 AM

This should help you to make VLOOKUP return a 0 or a empty string "" instead of #N/A! ?"
Assume that the current formula is
=VLOOKUP(A1,$B$2:$D$100,3,False)
or more condensed
=VLOOKUP(A1,$B$2:$D$100,3,0)
One improved method is
=IF(COUNTIF($B$2:$B$100,A1), VLOOKUP(A1,$B$2:$D$100,3,0),””)
This way the VLOOKUP is only calculated if the value in A1 exists in B2:B100, and therefor, VLOOKUP won’t return a #N/A!

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 4th, 2002, 12:22 AM	#1
nguyenthehoat New Member Join Date: May 2002 Location: Yazaki Haiphong,Vietnam Posts: 15	I 'm using windows Xp and office XP. working in ware house section I have to control about 3000 kind of material. I usually use vlookup formula, it is very convenient but unfortunately, sometime data format in sheets automatically change to different type so my formula can not work correctly."#N/A". If I copy vlookup value from table ray it will return correct value. I tried to format vlookup value and the value in table ray in the same type (text, general,number) but my formula still doesn't work. It will take some months to input this data again. Please help me fix this problem. Many thanks in advance. Mr.The Hoat Yazaki Haiphong,Vietnam

May 4th, 2002, 01:59 AM	#2
rmtaylor Board Regular Join Date: Feb 2002 Location: Scotland Posts: 139	This should help you to make VLOOKUP return a 0 or a empty string "" instead of #N/A! ?" Assume that the current formula is =VLOOKUP(A1,$B$2:$D$100,3,False) or more condensed =VLOOKUP(A1,$B$2:$D$100,3,0) One improved method is =IF(COUNTIF($B$2:$B$100,A1), VLOOKUP(A1,$B$2:$D$100,3,0),””) This way the VLOOKUP is only calculated if the value in A1 exists in B2:B100, and therefor, VLOOKUP won’t return a #N/A!