I haven't got your complete answer yet but have put some formulae in the attached to return the first match, and since you have upper and lower case Statuses it look as though you might want to differentiate between them. There's a 3rd formula (headed 'more robust') which would differentiate if you had a Job ABC and a status D, and another job AB with status CD; both would use concatenations to ABCD, but column I would concatenate to ABC¬D or AB¬CD, which are different. This may not apply at all maybe because all your jobs are 3 letters and all your statuses are single letters.


What I'm still struggling with is the COUNTIF part of your formula.
Could you attach the file again but put in manually the results you're expecting from the data that's in your sample file?
Also whether you want this to be case-sensitive or not.

Attached Files

msofficeforums49335sample_sheet.xlsx (13.9 KB, 10 views)