Hi there. I am struggling trying to use an array formula to recognize the existence of an empty cell to the left of a filled cell. Any help would be much appreciated.
The left hand column can contain various text or be empty. The text can be anything from a pre-defined list. The right hand column can contain various dates or be empty. I need to know if there are any occurrences where there is a date in a cell in the right hand column but the cell in the corresponding left hand column is empty. This would then return text such as "error" to alert the user that text is missing. I don't need to know what the values are, or what row. Just need to know that text is missing somewhere.
Both columns do not contain formulas. Some rows have no date and no text. Some have date or text only.
I have been using an array formula {=MATCH("text",IF(Q9:Q200<1000,P9:P200),0)} entered in another cell to recognize if there is text but no date. I need to do the reverse.
The formula would recognize that a row in the example below (row 4) has a date and no text. See .xlsm attached also.
eg
Col P Col Q
row 1 text
row 2
row 3 text date
row 4 date
row 5 text date
etc....
Thanks
Bookmarks