Skipping empty cells in Hlookup

[pichlermatthew]

We have a production activity sheet that folks fill out. The next day they display the sheet at their morning meeting. To trigger the Hlookup they enter today's date, and it brings up the previous day's data. I have a hidden cell with "today-1" to have a reference cell to back up one day. On days that we didn't have production on that line, how can I have the hlookup go back to the last day with data?

=IF(HLOOKUP(Data!$B$1,Data!$D$3:$NG$48,8,FALSE)="","",HLOOKUP(Data!$B$1,Data!$D$3:$NG$48,8,FALSE))

[AliGW]

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook (yellow banner: HOW TO ATTACH YOUR SAMPLE WORKBOOK). Screenshots are of little practical use as we cannot manipulate them.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.

[Phuocam]

Try this

=IF(HLOOKUP(Data!$B$1,Data!$D$3:$NG$48,8,FALSE)="",
HLOOKUP(Data!$B$1,Data!$D$3:$NG$48,7,FALSE),
HLOOKUP(Data!$B$1,Data!$D$3:$NG$48,8,FALSE))