Help Me !

nguyenthehoat · May 6th, 2002, 02:35 AM

Dear My friend,
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

Ivan F Moala · May 6th, 2002, 02:44 AM

Was this reply any help to you ?

http://www.mrexcel.com/board/viewtop...c=7278&forum=2

KnAsTa · May 6th, 2002, 02:44 AM

I'm going to need more information about your problem.......... Its not really obvious to me what you're exactly trying to do.........

But have u tried an IF(ISNA( or IF(ISNUMBER( - then do something? It will get rid of your errors.........but this is probably not the answer you were looking for.......but please write back with a better explination.

Cheers.
KnAsTa

Aladin Akyurek · May 6th, 2002, 03:22 AM

The underlying formats (that is, the format Excel sees) of the lookup-values and the values that make up the first column of your lookup table must agree.

If the first column of your lookup table has expectedly numeric values (like 24, 2-Jan-02, 12:15) and alphanumeric values (like 24AR-8, Nguyen, kar897, 0034), you might have trouble to keep them in their appropriate undelying format.

Lets say that E2:J2000 houses your lookup table.

Try the following to see where the problem is:

(1)

=MATCH(A1,E2:E2000,0)

(2)

=MATCH(A1&"",E2:E2000,0)

If (1) ends up in #N/A, while (2) returns a number, you have a lookup value with number as underlying format, while the lookup table has it with text as underlying format. You can use this diagnostic information to repair the uderlying format of the first column of your lookup table or adjust the underlying format of the lookup values that you feed to your lookup formula.

In the meantime, you can use an expensive solution as in:

=VLOOKUP(IF(ISNUMBER(MATCH(A1,E2:E2000,0)),A1,A1&""),E2:J2000,0)

This formula can still return #N/A, just because A1 does not exist in the lookup table neither as text nor as number.

By the way, I'd suggest deleting your latest repost, if no one has replied yet to that thread.

nguyenthehoat · May 7th, 2002, 03:44 AM

Thanks for your tip.
I have tried but it still did not work
Please help me.
F.Y.I. I reposted with subject " Format value automatically changed".

Brgds,
T.Hoat

nguyenthehoat · May 11th, 2002, 02:29 AM

Dear Aladin Akyurek,

Thanks for your help.
My vlookup values are kar897, 0034, 1241, so 0034, 1241 need changing to text.
After changing table_ray format value to text ( = E2&"") and use the formula

=VLOOKUP(IF(ISNUMBER(MATCH(A1,E2:E2000,0)),A1,A1&""),E2:J2000,2,0)

If Vlookup value is text or number, formula will still return true.
I do not have to input data again. How excellent is !!

Kind brgds,
The Hoat

Chris Davison · May 11th, 2002, 05:49 AM

I notice this is the THIRD version of this thread, with 3 sets of different people working on it.

PLEASE only post your question once.

Obviusly Myself and Yogi hadn't seen this thread and spent some time looking at your problem oblivious to the fact that Aladin and others were looking at it too....

Fragmenting the help reduces the chances of getting it solved !

May 6th, 2002, 02:44 AM	#2
Ivan F Moala MrExcel MVP Join Date: Feb 2002 Location: Auckland, New Zealand Posts: 4,209	Was this reply any help to you ? http://www.mrexcel.com/board/viewtop...c=7278&forum=2 __________________ Kind Regards, Ivan F Moala From the City of Sails

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

May 6th, 2002, 02:35 AM	#1
nguyenthehoat New Member Join Date: May 2002 Location: Yazaki Haiphong,Vietnam Posts: 15	Dear My friend, 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 6th, 2002, 02:44 AM	#3
KnAsTa Board Regular Join Date: Apr 2002 Location: Australia Posts: 52	I'm going to need more information about your problem.......... Its not really obvious to me what you're exactly trying to do......... But have u tried an IF(ISNA( or IF(ISNUMBER( - then do something? It will get rid of your errors.........but this is probably not the answer you were looking for.......but please write back with a better explination. Cheers. KnAsTa

May 6th, 2002, 03:22 AM	#4
Aladin Akyurek MrExcel MVP Join Date: Feb 2002 Location: The Hague Posts: 50,317	The underlying formats (that is, the format Excel sees) of the lookup-values and the values that make up the first column of your lookup table must agree. If the first column of your lookup table has expectedly numeric values (like 24, 2-Jan-02, 12:15) and alphanumeric values (like 24AR-8, Nguyen, kar897, 0034), you might have trouble to keep them in their appropriate undelying format. Lets say that E2:J2000 houses your lookup table. Try the following to see where the problem is: (1) =MATCH(A1,E2:E2000,0) (2) =MATCH(A1&"",E2:E2000,0) If (1) ends up in #N/A, while (2) returns a number, you have a lookup value with number as underlying format, while the lookup table has it with text as underlying format. You can use this diagnostic information to repair the uderlying format of the first column of your lookup table or adjust the underlying format of the lookup values that you feed to your lookup formula. In the meantime, you can use an expensive solution as in: =VLOOKUP(IF(ISNUMBER(MATCH(A1,E2:E2000,0)),A1,A1&""),E2:J2000,0) This formula can still return #N/A, just because A1 does not exist in the lookup table neither as text nor as number. By the way, I'd suggest deleting your latest repost, if no one has replied yet to that thread.

May 7th, 2002, 03:44 AM	#5
nguyenthehoat New Member Join Date: May 2002 Location: Yazaki Haiphong,Vietnam Posts: 15	Thanks for your tip. I have tried but it still did not work Please help me. F.Y.I. I reposted with subject " Format value automatically changed". Brgds, T.Hoat

May 11th, 2002, 02:29 AM	#6
nguyenthehoat New Member Join Date: May 2002 Location: Yazaki Haiphong,Vietnam Posts: 15	Dear Aladin Akyurek, Thanks for your help. My vlookup values are kar897, 0034, 1241, so 0034, 1241 need changing to text. After changing table_ray format value to text ( = E2&"") and use the formula =VLOOKUP(IF(ISNUMBER(MATCH(A1,E2:E2000,0)),A1,A1&""),E2:J2000,2,0) If Vlookup value is text or number, formula will still return true. I do not have to input data again. How excellent is !! Kind brgds, The Hoat

May 11th, 2002, 05:49 AM	#7
Chris Davison MrExcel MVP Join Date: Feb 2002 Location: Millbank, London, UK Posts: 1,790	I notice this is the THIRD version of this thread, with 3 sets of different people working on it. PLEASE only post your question once. Obviusly Myself and Yogi hadn't seen this thread and spent some time looking at your problem oblivious to the fact that Aladin and others were looking at it too.... Fragmenting the help reduces the chances of getting it solved !