Help on MATCH function


I think I might need help with using MATCH but not 100% sure if its the right way to go

I have a sheet with a column named Job Number and another column named Status

I am looking to search for any matches in the job number column and if there is a match AND the status column in this matched row is closed I want to set my initial column status also to be closed.

Hope that makes sense !!

Any help appreciated


  • Hi @Dan Crimmins

    Is this all in the same sheet? Or are these different sheets? Generally, it may be better to use COUNTIFS function as follows:

    =IF(COUNTIFS({Job Number}, [Job Number]@row, {Status}, [Status]@job) >1, xxx, yyy)

    If you want to set the initial column status automatically you can either have a formula to do so (i.e. use "Closed" instead of xxx above) or use workflow - depends on situation.

    Hope this helps