Finding first blank cell

Topic

New Reply

I have been trying to set up a worksheet function to find the first blank cell
in a range of 17 cells. The attached worksheet shows what I have tried and the
results are very confusing. I had thought the MATCH function would work but it
gives very erratic results.

In the example,the formula marked red is whsat I am trying to do. The rest are just showing different result for the Match function. Also examples showingg thatg ISBLANK and IF seem to work ok.

Thyer Offset formula seems to go one too far (Showing XXXX) when all cells are blank but seems to work (backwards) if a value is placed in Q3 or J3!!

I had thought that the Match function worked from left to right but it appears to bw working right to left in this workbook.

Can someone help me here???

Thanks, Robbie

Reply | Quote

Replies

How about the array formula (confirm with ctrl-shift-enter):

=MIN(IF(ISBLANK(A3:Q3),COLUMN(A3:Q3)))

Steve

Reply | Quote

Just for variation:
=MATCH(TRUE,ISBLANK($A$3:$Q$3),0)
array-entered, or:
=MATCH(TRUE,INDEX(ISBLANK($A$3:$Q$3),0),0)
entered normally.

Reply | Quote

Thank you Steve and Rory, I will try these today and will let you know…

… Robbie

Reply | Quote

Hi,

just another variation for finding which address is it:
{=ADDRESS(ROW(A1),COLUMN(INDEX(A1:Q1,1,MATCH(TRUE,LEN(A1:Q1)=0,0))),4)}

Example of this is here: http://excel-example.com/other-tutorial/address-of-the-first-empty-cell-in-a-row

Reply | Quote