Nested IF Statement using LEN

rplohocky

Active Member
Joined
Sep 25, 2005
Messages
292
Office Version
  1. 365
Platform
  1. Windows
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?
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
What do you want to do if A1 is not a blank?

Also you say "greater than" blank, what do you mean by that?
 
Upvote 0
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.
 
Upvote 0
So try this then:

Code:
=IF(LEN(VLOOKUP(A1,Sheet1!1:1048576,3,FALSE))=0,"",IF(ISBLANK(A1),"",VLOOKUP(A1,Sheet1!1:1048576,3,FALSE)))
 
Upvote 0
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?
 
Upvote 0
Try this:
Code:
=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))))
 
Upvote 0
Try this:
Code:
=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.
 
Upvote 0
Ok one last try from me, how about this:
Code:
=IF(OR(ISBLANK(A1),(ISNA(VLOOKUP(A1,Sheet1!1:1048576,3,FALSE)))),"",VLOOKUP(A1,Sheet1!1:1048576,3,FALSE))
 
Upvote 0
Sorry during testing took out one section try this instead:

Code:
=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)))
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

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