Multiple IF and Functions together??

Hi,
I was able to find resolution to two different issues with the search mechanism, but I need to stick them together.
The issue:
Data in column A worksheet1 has a character(A)and 10 numbers with a leading 0.
Column A worksheet2 has no character, but a leading zero.
using VLOOKUP to populate column B worksheet1 with data from column B worksheet2 without the #N/A
Can the IF(ISNA function be linked with a LEN function??
Have tried =Right, but the leading zero is a problem.
I know this will be a lengthy formula.

Thanks
Keith

On 2002-03-02 09:18, Anonymous wrote:
Hi,
I was able to find resolution to two different issues with the search mechanism, but I need to stick them together.
The issue:
Data in column A worksheet1 has a character(A)and 10 numbers with a leading 0.
Column A worksheet2 has no character, but a leading zero.
using VLOOKUP to populate column B worksheet1 with data from column B worksheet2 without the #N/A
Can the IF(ISNA function be linked with a LEN function??
Have tried =Right, but the leading zero is a problem.
I know this will be a lengthy formula.

Thanks
Keith

Keith, is your data on worksheet2 (10 characters with leading zero) numeric or text? Assuming it is text, you could use this formula:

=IF(ISNA(VLOOKUP(RIGHT(A1,10),Sheet2!$A$1:$B$300,2)),"",VLOOKUP(RIGHT(A1,10),Sheet2!$A$1:$B$300,2))

If it is numeric, use this formula:

=IF(ISNA(VLOOKUP(VALUE(RIGHT(A1,10)),Sheet2!$A$1:$B$300,2)),"",VLOOKUP(VALUE(RIGHT(A1,10)),Sheet2!$A$1:$B$300,2))

Hope this helps you out.