I am trying to come up with formula in excel for the following:

I have several columns that have the following criteria Civil Est 1 through to Civils Est 5 also Status Est 1 through to Status Est 5

In each of the Civils Est there is Completed or Not Completed or the cell is blank. In the Status Est there are a series of numbers.

What I want is if one of the Civils Est column has the statement not complete I want it to return the value from the relevant Status Est column.

enter image description here

  • What would help is some test data and expected output in tabular form so we can copy and paste it into excel and test formulas. Please Edit your original post and include the data and expected outcome. – Scott Craner Sep 12 '16 at 16:44
  • 2
    So, if something matches something, then do something? – Raystafarian Sep 12 '16 at 16:54
  • Scott will send over shortly – Rob Solomkn Sep 12 '16 at 16:57
  • You just want the first "not completed"? – Scott Craner Sep 12 '16 at 17:34

Use this formula:

=INDEX(A2:J2,MATCH("Not Completed",A2:J2,0)-1)

This will find the first Not Completed and return the value directly to the Left of it.

enter image description here

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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