Vlookup - ignore blank cells

[TheTassieBFG]

Hi again hoping someone can help with the latest problem in my pet project.

I am creating annual planner style spreadsheet and am having issues with a vlookup formula

current formula
=IF(ISERROR(VLOOKUP(O143,'Holidays & Observances'!$B$3:$D$52,2,FALSE))=TRUE,"",VLOOKUP(O143,'Holidays & Observances'!$B$3:$D$52,2,FALSE))

which removes the #N/A error for those dates that do not have a holiday but as can be seen in the first table returns the name of the first blank cell.

I understand that vlookup is returning the first cell with the same value as (O15) which is blank, however having lots of this "New Years Holiday" in cells with a blank date in Table 1 is not helpful. is there a way to have vlookup ignore blank cells?

The holidays in these later cells are ones that only happen if the actual holiday (in this case new years) falls on the weekend and the public holiday falls on the next working day. There are four holidays were this occurs so they need to be kept in the range but ignored for the years they are not used.

Table 1: Calendar (P15 - should be blank)

[TABLE="class: grid, width: 299"]
<tbody>[TR]
[TD][/TD]
[TD]O[/TD]
[TD]P[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD][/TD]
[TD]New Years Day Holiday[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]01[/TD]
[TD]New Years Day[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]23[/TD]
[TD]02[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]24[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]25[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]26[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]




[TABLE="width: 235"]
<tbody>[TR]
[TD]Table 2:Holidays & Observances[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="class: grid, width: 382"]
<tbody>[TR]
[TD][/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]public holidays[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]01 Jan 14[/TD]
[TD]New Years Day[/TD]
[TD]h[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]06 Jan 14[/TD]
[TD]Epiphany[/TD]
[TD]co[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]08 Jan 14[/TD]
[TD]Devonport Cup[/TD]
[TD]o[/TD]
[/TR]
[TR]
[TD="align: right"]....[/TD]
[TD="align: right"]MORE[/TD]
[TD]DATES [/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]41[/TD]
[TD="align: right"][/TD]
[TD]New Years Day Holiday[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]any assistance greatly appreciated
Andrew [/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[TheTassieBFG]

SOLVED
Sorry could not see how to edit thread to show in thread title

Had been playing around with a formula tonight but hadn't been able to make it work, go to bed and it strikes me that I had been telling it to look in the wrong place for a blank cell

new formula (based on one found on the web)
=IF(OR(O7="",ISERROR(VLOOKUP(O7,'Holidays & Observances'!$B$3:$D$52,2,FALSE)))=TRUE,"",VLOOKUP(O7,'Holidays & Observances'!$B$3:$D$52,2,FALSE))

i had been trying to to have the formula ignore blanks in Table2: Holidays & Observances when it should have just been ignoring the blanks in table 1.

thanks for looking - now back to bed for the remaining hours of the night
Andrew
[TABLE="width: 199"]
<colgroup><col width="199"></colgroup><tbody>[TR]
[TD="class: xl65, width: 199"][TABLE="width: 199"]
<colgroup><col width="199"></colgroup><tbody>[TR]
[TD="class: xl65, width: 199"] [/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[/TR]
</tbody>[/TABLE]

 

[Redwolfx]

The Quick answer would be to also us an IsBlank and a 2nd if statement.

=IF(ISBLANK(O143),"",IF(ISERROR(VLOOKUP(O143,'Holidays & Observances'!$B$3:$D$52,2,FALSE))=TRUE,"",VLOOKUP(O143,'Holidays & Observances'!$B$3:$D$52,2,FALSE)))

Or if you are using Excel 2007 or later


=IFERROR(IF(ISBLANK(O143),"",VLOOKUP(O143,'Holidays & Observances'!$B$3:$D$52,2,FALSE)),"")