Need Index/Match to find the first non-blank cell and return the value in the top row

I have a list of clients (one row per client) and their data by month (one column per month), I need an Index/Match or Lookup formula to find the first non-blank cell on the row and return the month it corresponds to (The months are in row 1).

I have attached the sheet I am using to test it.

Hello and welcome to the forum.

Try this:

B2 =INDEX(C$1:I$1,INDEX(MATCH(TRUE,C2:I2<>"",0),0))

*boom*

Looks like a winner. Thanks so much! Three minutes! Amazing

I will have to unpick it now as neeeeeeeeeeeed to know how/why it works

Happy to help. Thanks for the rep!

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Note that this formula can also be array-entered like this: =INDEX(C$1:I$1,MATCH(TRUE,C2:I2<>"",0)) Ctrl Shift Enter

Basically, it forms an array of logical tests {C2<>"",D2<>"",E2<>"",F2<>"",G2<>"",H2<>"",I2<>""} which turns into {FALSE,FALSE,TRUE,TRUE,FALSE,TRUE,FALSE}.
The MATCH function then returns the first instance of the lookup value (which was TRUE in this case) which is in the third column.

The INDEX function then returns the third value from the range C1:I1, which is "Mar".

f

ormulas
with massive input

Formula:

Please Login or Register  to view this content.

without

Formula:

Please Login or Register  to view this content.