# GOOGLE SHEETS: How to find and return the nth non blank bell value based on today's date column



## songo (May 26, 2017)

Hi All,
I have tricky question, want to combine HLOOKUP with Array & Index, functions.
What I am trying to accomplish, I have sheet that’s needs to be updated every day automatically, on the top of the sheet I have =today() function.
Now I have second sheet, it’s actually scheduler with team stuff, dates and planned additional activities in hours. 
And here is the trick… not all people have such a activities, some of them don’t and cell in blank.
I want excel to find today’s column in scheduler (obviously HLOOKUP – but instead of putting index (row number) I want excel to pick up nth non blank value.
Below is link to table of scheduler example,
today - =today() is 26th I want excel to show number 2.
Tomorrow- =today() will be 27th I want excel to show number 3.

https://drive.google.com/open?id=0ByJQKthhcDhuRTdfS3RmMjgtdUk


On the link below I have found formula to pickup nth blank value, but it stick to specific range I need that range to be changed based on today’s date column picked up by HLOOKUP or some other function.
https://www.extendoffice.com/documents/excel/3970-excel-find-nth-non-blank-cell.html


----------



## James006 (May 26, 2017)

*Re: How to find and return the nth non blank bell value based on today's date column*

Hi,

Why don't you post your current formula ...

and explain what would need to be modified ...


----------



## songo (May 26, 2017)

*Re: How to find and return the nth non blank bell value based on today's date column*

ok, i will try but it's not easy cause I don't have an idea how to combine 2 formulas.

=ArrayFormula(index('Scheduler'!D:D,small(row('Scheduler'!D:D)+(100*('Scheduler'!D:D="")),4))&"")

formula above works fine, it shows 4th non empty value in column D in Scheduler- sheet.
What I need to upgrade, is that formula changes range every day depending on today's date, 26th of may is column D in Scheduler and it's fine for today,
but tomorrow I want formula to pickup next column as a range witch is E as for 27th of May.


----------



## songo (May 26, 2017)

*Re: How to find and return the nth non blank bell value based on today's date column*

And here is HLOOKUP function.
=HLOOKUP(B2,'Scheduler'!3:25,4), 
where B2 is cell with =today() function.
In scheduler row 3 is filled with dates, so hlookup perfectly find today's column, and now instead of putting row_index_num, (for ex. 4) i want excel to show me (1st/2nd/3rd etc) non empty value in finded todays column.

Hope it's clear now))) uffff


----------



## 63falcondude (May 26, 2017)

*Re: How to find and return the nth non blank bell value based on today's date column*

songo, it is important to specify that you are using Google Sheets.

While Excel and Google Sheets are similar, they also have their differences.


----------



## James006 (May 26, 2017)

*Re: How to find and return the nth non blank bell value based on today's date column*

Hello,

An attempt would be replace 
'Scheduler'!D:D
by
Indirect("'Scheduler'!"&LEFT(ADDRESS(1,day(today())-22,4),1)&":"&LEFT(ADDRESS(1,day(today())-22,4),1))

Hope this will help


----------



## James006 (May 26, 2017)

*Re: How to find and return the nth non blank bell value based on today's date column*

@63falcondude

Thanks for pointing out OP is using Google Sheets ...

I do not have the slightest idea about Google Sheets ... and most probably the Indirect() function will not work ...


----------



## songo (May 26, 2017)

*Re: How to find and return the nth non blank bell value based on today's date column*

guys... really sorry for not mentioning google sheets, I am actually trying on both, but eventually it should be on google sheets.
James, i have tried your advise...

=ArrayFormula(index(Indirect("'Scheduler'!"&LEFT(ADDRESS(1,day(B2)-22,4),1)&":"&LEFT(ADDRESS(1,day(B2)-22,4),1)),
small(row(Indirect("'Scheduler'!"&LEFT(ADDRESS(1,day(B2)-22,4),1)&":"&LEFT(ADDRESS(1,day(B2)-22,4),1)))
+(100*(Indirect("'Scheduler'!"&LEFT(ADDRESS(1,day(B2)-22,4),1)&":"&LEFT(ADDRESS(1,day(B2)-22,4),1))="")),2))&"")

it worked somehow in google sheets, showed me 2nd value in column D, if I change date for tomorrow is shows 2nd value from E.
However since it's not connected to dates in Scheduler, just to columns/rows,  i will face problem when month ends, because there will be cople of columns with summaries etc and then new month will start that's why i wanted to use HLOOKUP, because no matter what it just looks for same date and go down to column.


----------



## Joe4 (May 26, 2017)

*Re: How to find and return the nth non blank bell value based on today's date column*

_Moderator Note:_
I have moved the thread and updated the title to indicate it is a Google Sheets question.


----------



## songo (May 26, 2017)

*Re: How to find and return the nth non blank bell value based on today's date column*

Guys!!
I have figured out workaround, Index + nested ifs + match function.



=index(
if(Scheduler!5:5<>"",Scheduler!5:5,
if(Scheduler!6:6<>"",Scheduler!6:6,
if(Scheduler!7:7<>"",Scheduler!7:7,
if(Scheduler!8:8<>"",Scheduler!8:8,
Scheduler!9:9)))),match(B2,Scheduler!3:3,0))

Luckely i have only 25 people in my team so it would be 25 nestef ifs...not stylish but works... in case somebody has better looking idea i would appreciate help


----------



## songo (May 26, 2017)

Hi All,
I have tricky question, want to combine HLOOKUP with Array & Index, functions.
What I am trying to accomplish, I have sheet that’s needs to be updated every day automatically, on the top of the sheet I have =today() function.
Now I have second sheet, it’s actually scheduler with team stuff, dates and planned additional activities in hours. 
And here is the trick… not all people have such a activities, some of them don’t and cell in blank.
I want excel to find today’s column in scheduler (obviously HLOOKUP – but instead of putting index (row number) I want excel to pick up nth non blank value.
Below is link to table of scheduler example,
today - =today() is 26th I want excel to show number 2.
Tomorrow- =today() will be 27th I want excel to show number 3.

https://drive.google.com/open?id=0ByJQKthhcDhuRTdfS3RmMjgtdUk


On the link below I have found formula to pickup nth blank value, but it stick to specific range I need that range to be changed based on today’s date column picked up by HLOOKUP or some other function.
https://www.extendoffice.com/documents/excel/3970-excel-find-nth-non-blank-cell.html


----------



## songo (May 26, 2017)

*Re: How to find and return the nth non blank bell value based on today's date column*

have counted chikens before they hatched... using example above i am able only to pickup 1st non blank value in column.


----------

