Multiple IF and Functions together??

G

Guest

Guest
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
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
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.
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,317
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top