Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with LEFT function
Hello,
I got two sheets on the same file. Sheet1 has column with numeric/ general data with 3 leading zero in the beginning, ex: 000123456. Sheet2 is a template with column same data except only six digits 123456. I did a Vlookup to compare this column and output the other column. My formula is: =VLOOKUP(RIGHT($I17,3),Data!$A:$P,2, FALSE) But it is not outputing the data instead shown "#N/A". What I attend to do is compare the last 6 digits, if match return the corresponding value in column 2. Thanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with LEFT function
Is the value in sheet1 truly 000123456, or is it 123456 with a format
of 000000000? If it is the latter, you can do just a standard vlookup without the right function because the values will be the same. If it is truly a 9 digit value in the cell with leading zeros, you could use something like =VLOOKUP(TEXT(I1,"000000000"),F:F,1,FALSE). Cam wrote: Hello, I got two sheets on the same file. Sheet1 has column with numeric/ general data with 3 leading zero in the beginning, ex: 000123456. Sheet2 is a template with column same data except only six digits 123456. I did a Vlookup to compare this column and output the other column. My formula is: =VLOOKUP(RIGHT($I17,3),Data!$A:$P,2, FALSE) But it is not outputing the data instead shown "#N/A". What I attend to do is compare the last 6 digits, if match return the corresponding value in column 2. Thanks |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with LEFT function
Right returns a text string while I'll bet you have numbers in your table.
You are trying to compare apples to oranges. Give this a whirl... =VLOOKUP(value(RIGHT($I17,6)),Data!$A:$P,2, FALSE) -- HTH... Jim Thomlinson "Cam" wrote: Hello, I got two sheets on the same file. Sheet1 has column with numeric/ general data with 3 leading zero in the beginning, ex: 000123456. Sheet2 is a template with column same data except only six digits 123456. I did a Vlookup to compare this column and output the other column. My formula is: =VLOOKUP(RIGHT($I17,3),Data!$A:$P,2, FALSE) But it is not outputing the data instead shown "#N/A". What I attend to do is compare the last 6 digits, if match return the corresponding value in column 2. Thanks |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with LEFT function
Note that assuming the first 3 digits are always Zero you could drop the
Right function all together... =VLOOKUP(value($I17),Data!$A:$P,2, FALSE) -- HTH... Jim Thomlinson "Jim Thomlinson" wrote: Right returns a text string while I'll bet you have numbers in your table. You are trying to compare apples to oranges. Give this a whirl... =VLOOKUP(value(RIGHT($I17,6)),Data!$A:$P,2, FALSE) -- HTH... Jim Thomlinson "Cam" wrote: Hello, I got two sheets on the same file. Sheet1 has column with numeric/ general data with 3 leading zero in the beginning, ex: 000123456. Sheet2 is a template with column same data except only six digits 123456. I did a Vlookup to compare this column and output the other column. My formula is: =VLOOKUP(RIGHT($I17,3),Data!$A:$P,2, FALSE) But it is not outputing the data instead shown "#N/A". What I attend to do is compare the last 6 digits, if match return the corresponding value in column 2. Thanks |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with LEFT function
The second parameter in RIGHT is the number of characters to take, not
the characters to ignore. I think you want something like: =VLOOKUP(RIGHT($I17,6),Data!$A:$P,2, FALSE) If you wanted to do it the other way round, you could search for =VLOOKUP("000"&Data!A1, etc Hope this helps. Pete On Oct 12, 7:04 pm, Cam wrote: Hello, I got two sheets on the same file. Sheet1 has column with numeric/ general data with 3 leading zero in the beginning, ex: 000123456. Sheet2 is a template with column same data except only six digits 123456. I did a Vlookup to compare this column and output the other column. My formula is: =VLOOKUP(RIGHT($I17,3),Data!$A:$P,2, FALSE) But it is not outputing the data instead shown "#N/A". What I attend to do is compare the last 6 digits, if match return the corresponding value in column 2. Thanks |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with LEFT function
What I attend to do is compare the last 6 digits
The last 6 characters would be RIGHT($I17,6) if match return the corresponding value in column 2. Then I'd think you could reduce the lookup range to just $A:$B, but i doubt that would make any significant impact. HTH, "Cam" wrote in message ... Hello, I got two sheets on the same file. Sheet1 has column with numeric/ general data with 3 leading zero in the beginning, ex: 000123456. Sheet2 is a template with column same data except only six digits 123456. I did a Vlookup to compare this column and output the other column. My formula is: =VLOOKUP(RIGHT($I17,3),Data!$A:$P,2, FALSE) But it is not outputing the data instead shown "#N/A". What I attend to do is compare the last 6 digits, if match return the corresponding value in column 2. Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
MID function from right to left | Excel Worksheet Functions | |||
How to nest a left function within a sumif function? | Excel Worksheet Functions | |||
Left Function | Excel Discussion (Misc queries) | |||
Left vs Left$ function | Excel Discussion (Misc queries) | |||
HOW DO I NEST THE VLOOKUP FUNCTION WITH THE LEFT FUNCTION | Excel Worksheet Functions |