Recent content by garfieldandew

Yes, you can do this with a dynamic-array approach. For example, =XLOOKUP(A2,TOCOL(E2:M100),TOCOL(N2:N100), "Not found") can search all the values in E, but the return range needs to be aligned with the corresponding rows. If you're using Microsoft 365, TOCOL makes this much easier because it...

You can do this with MAXIFS and XLOOKUP. Assuming IDs are in column A, dates in B, values in C, and the ID you want to look up is in E2, use: =XLOOKUP(1,(A2:A100=E2)*(B2:B100=MAXIFS(B2:B100,A2:A100,E2)),C2:C100) For A01, this returns 150. This works even if the data isn't sorted by date, so...

Yes, this is possible with a small VBA function. Excel’s standard COUNTIF doesn’t directly count cells based on their fill color, but you can create a UDF that compares the cell color to a reference cell. For example, you could use =CountIfColor(A1100,F1), where F1 has the red fill, then use...

I don't think they approve gambling