Formula help needed

Troy Bjelland

I need track what cylinder haven't been filled for over a year. On one sheet we track when cylinders are filled or transferred to different locations. I need to build a formula which will track the most current date when each cylinder was "refilled" or "Refilled and transferred to". Is there a way to use index match with ifs?

Any help would be appreciated

Thanks in advance

Nick Korna

Hi @Troy Bjelland,

You can avoid using INDEX/MATCH on this by going with MAX & COLLECT:

=MAX(COLLECT({Cylinder Log Range Date}, {Cylinder Log Cylinder ID}, [Cylinder ID]@row, {Cylinder Log Service Completed}, "Refilled"), COLLECT({Cylinder Log Range Date}, {Cylinder Log Cylinder ID}, [Cylinder ID]@row, {Cylinder Log Service Completed}, "Refill and transferred to"))

Sample data:

Output:

Hopefully the cross sheet range references etc. are straightforward enough to understand, but if you've any questions etc. then just ask! 😊