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?

 

[philwojo]

What do you want to do if A1 is not a blank?

Also you say "greater than" blank, what do you mean by that?

 

[rplohocky]

What do you want to do if A1 is not a blank?

Also you say "greater than" blank, what do you mean by that?

If A1 is not blank then execute the VLOOKUP. I'm using this formula in a template workbook which has the formula in a column dragged down 5000 rows and I don't want the formula responding back with #N/A all the way down the column. The IF statement prevents this from happening. The IF statement shows a blank cell unless there is something in column A.

Greater than blank just means if there is something in the cell its referring too.

 

[philwojo]

So try this then:

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

 

[rplohocky]

So try this then:

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

Absolutely Perfect! Thanks for the help!

 

[rplohocky]

Absolutely Perfect! Thanks for the help!

Hello philwojo,
I had some time to test this formula and found out that its not working as I would like it. It seemed to work when the VLOOKUP finds the matching number in the search page (Sheet1) but it there is not a matching number then it returns a #N/A. I need this to be entirely blank if its doesn't find anything. Any ideas?

 

[philwojo]

Try this:

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

 

[rplohocky]

Try this:

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

Sorry no this doesn't seem to be working either. It keeps on bringing back #N/A if the number I am looking up is not found in Sheet1 column A.

All I'm trying to do have a blank cell no matter what the VLOOKUP finds or doesn't find.

Thanks.

 

[philwojo]

Ok one last try from me, how about this:

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

 

[philwojo]

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