# First and Last Instance within Date Range

1. ## First and Last Instance within Date Range

Hi,

I'm trying to return the first and last instance of a blank cell within a date range without doing an array formula (the master spreadsheet is very large, so trying to make it as "quick" as possible).

R1: Start date in range to be searched (will be manually updated)
R2: End date in range to be searched (will be manually updated)

Column O: List first instance of blank cell within date range identified in R1 (hoping to automate)
Column P: List last instance of blank cell within date range identified in R2 (hoping to automate)

I've manually entered the values in columns O&P in red text, so you can see what values I'm hoping to return. Any help would be greatly appreciated!

Thank you!

2. ## Re: First and Last Instance within Date Range

Try these:

O4 =IFERROR(INDEX(D\$3:L\$3,INDEX(MATCH(1,(D\$3:L\$3>=S\$1)*(D\$3:L\$3<=S\$2)*(D4:L4=""),0),0)),"")

P4 =IFERROR(LOOKUP(2,1/((D\$3:L\$3>=S\$1)*(D\$3:L\$3<=S\$2)*(D4:L4="")),D\$3:L\$3)+6,"")

Drag both formulas down.

3. ## Re: First and Last Instance within Date Range

Thanks! It seems to be working. Out of curiosity, is there another way to achieve the formula in column P without using a look-up function?

4. ## Re: First and Last Instance within Date Range

Why do you wish to avoid the LOOKUP function?

There are currently 1 users browsing this thread. (0 members and 1 guests)