I am trying to get the value in red below which corresponds to the first not empty cell in the line and to have the date corresponding for each :

enter image description here

I have tried this formula but it did not work for some reason :

=INDEX('Charge 2018 par ressource'!E16:P16;1;MATCH(1;(1-ISBLANK('Charge 2018 par ressource'!E16:P16));0))

Any ideas on what matricial formula to use for that matter ?

  • How exactly it doesn't work? Have you tried evaluating it? Have you entered it as an array formula? – Máté Juhász Dec 13 '17 at 13:43
  • Yes, I have tried evaluating the formula but it just doens't help me finding the solution. Also, if it would work, it would only bring me back the value of the first non-empty cell, but not the date at which the first non-empty cell is. Which is what i am looking for – MopMop Dec 13 '17 at 13:45

You are looking for the first nonblank cell with (1-ISBLANK('Charge 2018 par ressource'!E16:P16)) None of those are blank.

ISBLANK looks for truly blank cells, nothing in them, no formula, no 0. You are looking for the first non 0 cell.

So use:

(1-('Charge 2018 par ressource'!E16:P16=0))

Also, if you want the date you need the first reference to reference the date row not the whole field, I assume that row is row 1 here, if different change to the row with the dates:

'Charge 2018 par ressource'!E$1:P$1

So your array formula would be:

=INDEX('Charge 2018 par ressource'!E$1:P$1;MATCH(1;(1-('Charge 2018 par ressource'!E16:P16=0));0))

Being an array formula it must be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode.

You could also use this formula that gets entered normally:

=INDEX('Charge 2018 par ressource'!$1:$1;AGGREGATE(15;6;COLUMN('Charge 2018 par ressource'!E16:P16)/('Charge 2018 par ressource'!E16:P16<>0);1))

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.