Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2002: How to make the formula works ?
Dear Sir,
I use =MID(A1,2,6) to convert the original reference numbers to six digit reference in column B. After that I use VLOOKUP formula in cells B10 to B15 to lookup for the amount in Table A. Table A A B C Original Ref 6 digits Ref Amount 1 0424907001 424907 277.37 2 0424908001 424908 515.97 3 0424909001 424909 1,237.46 4 0424910001 424910 2,028.02 5 0424911001 424911 1,237.46 6 0424912001 424912 586.85 Table B Ref Amount 10 424905 =VLOOKUP(A10,A$1:C$6,3,FALSE) 11 424906 #N/A 12 424907 #N/A 13 424908 #N/A 14 424909 #N/A 15 424910 #N/A However all the cells return with #N/A even though B1:B6 and A10:A15 are values. I try to use =Index(C$1:C$6,Match(B10,B$1:B$6,0)) for the lookup but it does not work either. For your information the original 10 digits reference in table A was generated by another business system in Excel Format. May I know what goes wrong with this and how to overcome this problem? Thanks Low -- A36B58K641 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2002: How to make the formula works ?
=MID(A1,2,6)
return text that looks like a number. =--MID(A1,2,6) will coerce that text back to a number. Mr. Low wrote: Dear Sir, I use =MID(A1,2,6) to convert the original reference numbers to six digit reference in column B. After that I use VLOOKUP formula in cells B10 to B15 to lookup for the amount in Table A. Table A A B C Original Ref 6 digits Ref Amount 1 0424907001 424907 277.37 2 0424908001 424908 515.97 3 0424909001 424909 1,237.46 4 0424910001 424910 2,028.02 5 0424911001 424911 1,237.46 6 0424912001 424912 586.85 Table B Ref Amount 10 424905 =VLOOKUP(A10,A$1:C$6,3,FALSE) 11 424906 #N/A 12 424907 #N/A 13 424908 #N/A 14 424909 #N/A 15 424910 #N/A However all the cells return with #N/A even though B1:B6 and A10:A15 are values. I try to use =Index(C$1:C$6,Match(B10,B$1:B$6,0)) for the lookup but it does not work either. For your information the original 10 digits reference in table A was generated by another business system in Excel Format. May I know what goes wrong with this and how to overcome this problem? Thanks Low -- A36B58K641 -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2002: How to make the formula works ?
On Jun 15, 9:34 am, Mr. Low wrote:
Dear Sir, I use =MID(A1,2,6) to convert the original reference numbers to six digit reference in column B. After that I use VLOOKUP formula in cells B10 to B15 to lookup for the amount in Table A. Table A A B C Original Ref 6 digits Ref Amount 1 0424907001 424907 277.37 2 0424908001 424908 515.97 3 0424909001 424909 1,237.46 4 0424910001 424910 2,028.02 5 0424911001 424911 1,237.46 6 0424912001 424912 586.85 Table B Ref Amount 10 424905 =VLOOKUP(A10,A$1:C$6,3,FALSE) 11 424906 #N/A 12 424907 #N/A 13 424908 #N/A 14 424909 #N/A 15 424910 #N/A However all the cells return with #N/A even though B1:B6 and A10:A15 are values. I try to use =Index(C$1:C$6,Match(B10,B$1:B$6,0)) for the lookup but it does not work either. For your information the original 10 digits reference in table A was generated by another business system in Excel Format. May I know what goes wrong with this and how to overcome this problem? Thanks Low -- A36B58K641 VLOOKUP will only look for the value in the first column of your selection. Try using your VLOOKUP formula with the range from B$1:C$6 instead of A$1:C$6. Tim |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2002: How to make the formula works ?
I see 2 potential problems. First, your formula
"=VLOOKUP(A10,A$1:C$6,3,FALSE)" is looking for a 6 digit value in a column of 10 digit values. Should probably be "=VLOOKUP(A10,B$1:C$6,2,FALSE)". Also, I suspect your values at A10 etc. are numbers and the values in column B are text. If so, I'd advise altering your "=MID(A1,2,6)" formula to "=--MID(A1,2,6)". Will "Mr. Low" wrote: Dear Sir, I see 2 potential problems. use =MID(A1,2,6) to convert the original reference numbers to six digit reference in column B. After that I use VLOOKUP formula in cells B10 to B15 to lookup for the amount in Table A. Table A A B C Original Ref 6 digits Ref Amount 1 0424907001 424907 277.37 2 0424908001 424908 515.97 3 0424909001 424909 1,237.46 4 0424910001 424910 2,028.02 5 0424911001 424911 1,237.46 6 0424912001 424912 586.85 Table B Ref Amount 10 424905 =VLOOKUP(A10,A$1:C$6,3,FALSE) 11 424906 #N/A 12 424907 #N/A 13 424908 #N/A 14 424909 #N/A 15 424910 #N/A However all the cells return with #N/A even though B1:B6 and A10:A15 are values. I try to use =Index(C$1:C$6,Match(B10,B$1:B$6,0)) for the lookup but it does not work either. For your information the original 10 digits reference in table A was generated by another business system in Excel Format. May I know what goes wrong with this and how to overcome this problem? Thanks Low -- A36B58K641 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2002: How to make the formula works ?
Hi,
The MID function creates a text return value, your lookup value are probably numbers. Both the lookup value and the lookup column for VLOOKUP must both be the same data types. You can do this at least two ways: 1. Replace the MID function with =VALUE(MID(A1,2,6) 2. Change the VLOOKUP's first argument to text VLOOKUP(TEXT(A10,"@"),A$1:C$6,3,FALSE) -- Cheers, Shane Devenshire "Mr. Low" wrote: Dear Sir, I use =MID(A1,2,6) to convert the original reference numbers to six digit reference in column B. After that I use VLOOKUP formula in cells B10 to B15 to lookup for the amount in Table A. Table A A B C Original Ref 6 digits Ref Amount 1 0424907001 424907 277.37 2 0424908001 424908 515.97 3 0424909001 424909 1,237.46 4 0424910001 424910 2,028.02 5 0424911001 424911 1,237.46 6 0424912001 424912 586.85 Table B Ref Amount 10 424905 =VLOOKUP(A10,A$1:C$6,3,FALSE) 11 424906 #N/A 12 424907 #N/A 13 424908 #N/A 14 424909 #N/A 15 424910 #N/A However all the cells return with #N/A even though B1:B6 and A10:A15 are values. I try to use =Index(C$1:C$6,Match(B10,B$1:B$6,0)) for the lookup but it does not work either. For your information the original 10 digits reference in table A was generated by another business system in Excel Format. May I know what goes wrong with this and how to overcome this problem? Thanks Low -- A36B58K641 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2002: How to make the formula works ?
Hello Dave,
Many thanks for your formula, the VLookup formula is now working Low -- A36B58K641 "Dave Peterson" wrote: =MID(A1,2,6) return text that looks like a number. =--MID(A1,2,6) will coerce that text back to a number. Mr. Low wrote: Dear Sir, I use =MID(A1,2,6) to convert the original reference numbers to six digit reference in column B. After that I use VLOOKUP formula in cells B10 to B15 to lookup for the amount in Table A. Table A A B C Original Ref 6 digits Ref Amount 1 0424907001 424907 277.37 2 0424908001 424908 515.97 3 0424909001 424909 1,237.46 4 0424910001 424910 2,028.02 5 0424911001 424911 1,237.46 6 0424912001 424912 586.85 Table B Ref Amount 10 424905 =VLOOKUP(A10,A$1:C$6,3,FALSE) 11 424906 #N/A 12 424907 #N/A 13 424908 #N/A 14 424909 #N/A 15 424910 #N/A However all the cells return with #N/A even though B1:B6 and A10:A15 are values. I try to use =Index(C$1:C$6,Match(B10,B$1:B$6,0)) for the lookup but it does not work either. For your information the original 10 digits reference in table A was generated by another business system in Excel Format. May I know what goes wrong with this and how to overcome this problem? Thanks Low -- A36B58K641 -- Dave Peterson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2002: How to make the formula works ?
Dear Sir,
Many thanks for your formula, the VLookup formula is now working Low -- A36B58K641 "roadkill" wrote: I see 2 potential problems. First, your formula "=VLOOKUP(A10,A$1:C$6,3,FALSE)" is looking for a 6 digit value in a column of 10 digit values. Should probably be "=VLOOKUP(A10,B$1:C$6,2,FALSE)". Also, I suspect your values at A10 etc. are numbers and the values in column B are text. If so, I'd advise altering your "=MID(A1,2,6)" formula to "=--MID(A1,2,6)". Will "Mr. Low" wrote: Dear Sir, I see 2 potential problems. use =MID(A1,2,6) to convert the original reference numbers to six digit reference in column B. After that I use VLOOKUP formula in cells B10 to B15 to lookup for the amount in Table A. Table A A B C Original Ref 6 digits Ref Amount 1 0424907001 424907 277.37 2 0424908001 424908 515.97 3 0424909001 424909 1,237.46 4 0424910001 424910 2,028.02 5 0424911001 424911 1,237.46 6 0424912001 424912 586.85 Table B Ref Amount 10 424905 =VLOOKUP(A10,A$1:C$6,3,FALSE) 11 424906 #N/A 12 424907 #N/A 13 424908 #N/A 14 424909 #N/A 15 424910 #N/A However all the cells return with #N/A even though B1:B6 and A10:A15 are values. I try to use =Index(C$1:C$6,Match(B10,B$1:B$6,0)) for the lookup but it does not work either. For your information the original 10 digits reference in table A was generated by another business system in Excel Format. May I know what goes wrong with this and how to overcome this problem? Thanks Low -- A36B58K641 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2002: How to make the formula works ?
Hello Tim,
By using the formula =--MID( ), I have solve the problem. Thank you anyway for your reply. Low -- A36B58K641 "Tim Shnell" wrote: On Jun 15, 9:34 am, Mr. Low wrote: Dear Sir, I use =MID(A1,2,6) to convert the original reference numbers to six digit reference in column B. After that I use VLOOKUP formula in cells B10 to B15 to lookup for the amount in Table A. Table A A B C Original Ref 6 digits Ref Amount 1 0424907001 424907 277.37 2 0424908001 424908 515.97 3 0424909001 424909 1,237.46 4 0424910001 424910 2,028.02 5 0424911001 424911 1,237.46 6 0424912001 424912 586.85 Table B Ref Amount 10 424905 =VLOOKUP(A10,A$1:C$6,3,FALSE) 11 424906 #N/A 12 424907 #N/A 13 424908 #N/A 14 424909 #N/A 15 424910 #N/A However all the cells return with #N/A even though B1:B6 and A10:A15 are values. I try to use =Index(C$1:C$6,Match(B10,B$1:B$6,0)) for the lookup but it does not work either. For your information the original 10 digits reference in table A was generated by another business system in Excel Format. May I know what goes wrong with this and how to overcome this problem? Thanks Low -- A36B58K641 VLOOKUP will only look for the value in the first column of your selection. Try using your VLOOKUP formula with the range from B$1:C$6 instead of A$1:C$6. Tim |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2002: How to make the formula works ?
Hello Shane,
Many thanks for your formulas, the VLookup formula is now working. Low -- A36B58K641 "ShaneDevenshire" wrote: Hi, The MID function creates a text return value, your lookup value are probably numbers. Both the lookup value and the lookup column for VLOOKUP must both be the same data types. You can do this at least two ways: 1. Replace the MID function with =VALUE(MID(A1,2,6) 2. Change the VLOOKUP's first argument to text VLOOKUP(TEXT(A10,"@"),A$1:C$6,3,FALSE) -- Cheers, Shane Devenshire "Mr. Low" wrote: Dear Sir, I use =MID(A1,2,6) to convert the original reference numbers to six digit reference in column B. After that I use VLOOKUP formula in cells B10 to B15 to lookup for the amount in Table A. Table A A B C Original Ref 6 digits Ref Amount 1 0424907001 424907 277.37 2 0424908001 424908 515.97 3 0424909001 424909 1,237.46 4 0424910001 424910 2,028.02 5 0424911001 424911 1,237.46 6 0424912001 424912 586.85 Table B Ref Amount 10 424905 =VLOOKUP(A10,A$1:C$6,3,FALSE) 11 424906 #N/A 12 424907 #N/A 13 424908 #N/A 14 424909 #N/A 15 424910 #N/A However all the cells return with #N/A even though B1:B6 and A10:A15 are values. I try to use =Index(C$1:C$6,Match(B10,B$1:B$6,0)) for the lookup but it does not work either. For your information the original 10 digits reference in table A was generated by another business system in Excel Format. May I know what goes wrong with this and how to overcome this problem? Thanks Low -- A36B58K641 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I make a graph using every 24 cells in Excel 2002? | Excel Discussion (Misc queries) | |||
convert works 4.5 spreadsheet to Excel 2002 (Office xp) | Excel Discussion (Misc queries) | |||
conversion of MS Works Spreadsheet to Excel 2002 Spreadsheet | Excel Discussion (Misc queries) | |||
How do I make range bar graphs in Excel 2002? | Charts and Charting in Excel | |||
how do I make excel 2003 my default spreadsheet over ms works spr. | Setting up and Configuration of Excel |