Excluding blank formula cell for an IF/VLOOKUP function

[rfreeman]

I have sheet containing a group of columns & rows that are based on a simple IFERROR formula, which populates my sheet based off of data from another sheet.
If the data rows in the other sheet are blank then my sheet returns empty cells as required.

The issue I now have is that I have added another group of VLookup formula cells that populate specific data from the other sheet. As some rows do not contain data I tried to use an IF formula with VLookup to return a blank cell if any rows are empty, however the the earlier mentioned IFERROR formula is causing my IF function to return #N/A results.

How can I get 1 lookup formula to exclude a cell if it has a blank value and only a formula listed?

I may not have explained this very well, so I have attached an example sheet showing the issue I have
- Columns F-I are the cells that I want to return a black cell based on the lookup of Column B

Thanks and regards
Rohan

 

[pecoflyer]

Without the source data it is difficult to test
Some hints : a cell containing a space " " is not the same as a null string ""
The IFERROR function is a Catch22 function, because it hides ALL errors, which might not be what you want, so be careful with its use
There is a difference between testing for a cell containing a value of 0 and an empty cell

 

[rfreeman]

Sorry @pecoflyer, I had the source data in a hidden tab. I have unhidden it in the attached version

 

[pecoflyer]

Something to start with
You might also want to have a look at https://www.contextures.com/xlexceltable01.html to organize your data

 

[p45cal]

change the formula in cell F11 (for example) of sheet Data summary - Flex to:
=IF($B11=0,"",VLOOKUP($B11,'Pipeline - Flex'!$A:$N,11,FALSE))

In columns A:E of that sheet you have zeroes, not blank cells, not errors; you have hidden the zeroes with conditional formatting.

 

[Peter Bartholomew]

A test that seems to work is
$B2<>0
Out of curiosity, what version of Excel are you using. Are you firmly wedded to pre Excel 2007 techniques?
[By that, I am thinking no Tables, oversized blocks of formulae, no dynamic arrays ... ]

 

[p45cal]

As Peter Bartholomew says, more up-to-date techniques, see attached where there's a Power Query (it's built-in to your version of Excel) offering based solely on data from columns A:J. To update the new table, right-click and choose Refresh.

 

[rfreeman]

Wow thanks so much everyone for the excellent feedback communication