Previously working INDEX MATCH formula now returns #VALUE when Re-entered

[Zilla3]

Hi All,

A previously working INDEX MATCH formula, which, as of one week after first entered, returns #VALUE! when re entered. The formula is meant to detect the first cell in an range with data and then match to the date for that particular column.

Formula: =INDEX($F$3:$BF$3,MATCH(1,IF(F10:BF10<>0,IF(F10:BF10<>"",1)),0))

The formula recalculates fine when the values are adjusted in the referenced cells, however I can no longer plug the formula into the same or different worksheets without it returning #VALUE!

Admittedly, I am not a well excel educated man. In my research I see that INDEX MATCH formulas are notoriously buggy. Its also worth noting that I have checked and my calculation settings are set to automatic. I've attached an example worksheet below. Does anyone happen to have an idea why my formulas are acting like this or have a work around. Any insight/help would be much appreciated.

Thank you.

[martindwilson]

that is an array formula and must be confirmed with ctrl+shift+enter
http://www.cpearson.com/excel/arrayformulas.aspx

[Ron Coderre]

It's an ARRAY FORMULA, completed by holding down CTRL and SHIFT when you press ENTER (instead of just pressing ENTER).
However, since hardly anybody I know remembers to C+S+E array formulas after editing them...I avoid them wherever practical.

Try this REGULAR FORMULA:

Please Login or Register  to view this content.

Is that something you can work with?

[Zilla3]

Thank you Martin! So glad it was my ignorance and not a technical issue.

Good day.

Z

[Zilla3]

Thank you, Ron! Also works perfectly.

Much appreciated.