Nested IF Statement using LEN

rplohocky

Board Regular
Joined
Sep 25, 2005
Messages
236
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?
 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

philwojo

Well-known Member
Joined
May 10, 2013
Messages
533
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

Board Regular
Joined
Sep 25, 2005
Messages
236
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

Well-known Member
Joined
May 10, 2013
Messages
533
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)))
 

rplohocky

Board Regular
Joined
Sep 25, 2005
Messages
236

ADVERTISEMENT

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

Absolutely Perfect! Thanks for the help!
 

rplohocky

Board Regular
Joined
Sep 25, 2005
Messages
236
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

Well-known Member
Joined
May 10, 2013
Messages
533

ADVERTISEMENT

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

rplohocky

Board Regular
Joined
Sep 25, 2005
Messages
236
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.
 

philwojo

Well-known Member
Joined
May 10, 2013
Messages
533
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))
 

philwojo

Well-known Member
Joined
May 10, 2013
Messages
533
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:

Watch MrExcel Video

Forum statistics

Threads
1,122,822
Messages
5,598,307
Members
414,224
Latest member
Crazy_FC

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
Top