Nested IF Statement using LEN

[rplohocky]

Hello,
I found this formula online which returns a blank cell if the VLOOKUP doesn't find anything in the 3rd column instead of ZERO.
<code>
=IF(LEN(VLOOKUP(A1,Sheet1!$1:$1048576,3,FALSE))=0,"",VLOOKUP(A1,Sheet1!$1:$1048576,3,FALSE))
</code>
The formula works great but need to add an additional argument. I would like to add
<code>
IF A1 is greater than blank then VLOOKUP(A1,Sheet1!$1:$1048576,3,FALSE
</code>

Can anyone help?

 

[rplohocky]

Sorry during testing took out one section try this instead:

=IF(OR(ISBLANK(A1),(ISNA(VLOOKUP(A1,Sheet1!1:1048576,3,FALSE)))),"",IF(VLOOKUP(A1,Sheet1!1:1048576,3,FALSE)="","",VLOOKUP(A1,Sheet1!1:1048576,3,FALSE)))

philwojo,
You are so close! Here's what's happening.
It works for the most part.
1. It doesn't bring back zero or #N/A. GOOD!
2. It brings back numbers if they are found. GOOD!
3. It brings back combinations such as number mixed with letters, regardless if they start with a number or letter. GOOD!
4. It WON'T bring back letters by themselves such as an email address. BAD

I can't figure out why it won't return letters but this will have to be looked at because one of my VLOOKUPS needs to return email addresses.

You are so close!!!

 

[philwojo]

The formula, as it works for me and brings back letters or numbers or emails, mine is not working as yours is.

I'm not sure why it would do that, maybe someone else has an idea.

 

[rplohocky]

philwojo,
You are so close! Here's what's happening.
It works for the most part.
1. It doesn't bring back zero or #N/A. GOOD!
2. It brings back numbers if they are found. GOOD!
3. It brings back combinations such as number mixed with letters, regardless if they start with a number or letter. GOOD!
4. It WON'T bring back letters by themselves such as an email address. BAD

I can't figure out why it won't return letters but this will have to be looked at because one of my VLOOKUPS needs to return email addresses.

You are so close!!!

Small update for you.
If you are looking up letters in the VLOOKUP it will then return letters. Weird!!!

 

[philwojo]

Again, mine does not function that way, what version of Excel are you using, maybe that has something to do with it, but the vlookup function, as far as I know, does not restrict what it will return, regardless of what is looked up.

I am headed home for the night, probably won't be checking this any more until next week, sorry.

 

[rplohocky]

Small update for you.
If you are looking up letters in the VLOOKUP it will then return letters. Weird!!!

philwojo,
Please let me retract the last post. I had multiples of the same number in the search column. When i made the search column have unique numbers your formula worked great! Sorry for the issue.

Thanks for your help! I believe this formula will do the trick!

 

[philwojo]

No problem, glad to help and hope it is what you need, if not post back and someone from this community will help I'm sure, great people here.