MATCH and INDEX function

[ckenn134]

Excel Snapshot1.jpg

The spreadsheet I have has conditional formatting whereby is a student achieves a certain criteria the instructor inserts a "y" and the cell automatically fills in green. What I have been trying to do is to find the cell which are blank and return a the title to the left of the blank cell.

The formula is:

{=INDEX($A$6:$A$79,MATCH(FALSE,ISTEXT($B$6:$B$79),0))}

The ISTEXT function lists all the cells which do not contain blanks and then looksup the value of this cell to the left. Similarly, I get a similar result if I put ISBLANK and change FALSE to TRUE. What I want to do is find the second FALSE result and find the next blank cell and return its lookup value.

I have even tried this function:
{=CELL("address",INDEX($A$6:$A$79,MATCH(FALSE,ISTEXT($B$6:$B$79),0)))}

The return the first blank cell which is A29. Firstly, is there a way where I can get excel to lookup the cell value for A29? Secondly, how can excel find the second cell address which is blank.

[dilipandey]

HI CKenn,

welcome to the forum.
Can you upload a small sample workbook (not image) along with expected results so that forum can give a quick try.. thx

Regards,
DILIPandey
<click on below * if this helps>

[ckenn134]

I have placed the formula results on a new sheet so you can see the result repeating

Maths Data.xlsx

[dilipandey]

Okay... use below formula:-

Please Login or Register  to view this content.

enter with key combination: ctrl shift enter

see attachment:- Maths Data.xlsx


Regards,
DILIPandey
<click on below * if this helps>

[ckenn134]

Thank you very much. I thought it might contain those function but uncertain how to formulate.

Regard
ckenn

[dilipandey]

you are welcome ckenn..

please mark this thread as [SOLVED].. thx


Regards,
DILIPandey
<click on below * if this helps>