Help with LEFT function

[Guest]

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

 

[JW]

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).

 

[Guest]

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)

 

[Guest]

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)

 

[Pete_UK]

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

 

[George Nicholson]

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,