Snap 2 Snap 3 I have a date in cell C2, created from a formula. I want to find that date in a grid (C10:Q20) and return the column header from the column it's found. Column headers are in C9:Q9. So, if cell C2 = 7/10/25 and that is found in cell G14, I need a formula that would return the text in G9. I'm currently using this function: =INDEX($C$9:$Q$9,, SUMPRODUCT(($C$10:$Q$20=C2)* (COLUMN($C$9:$Q$9)-COLUMN($B$2)))) and it is returning the correct value. However, I have another date in cell C4 to use to lookup a different date and find it's header label. I tried converting the formula I pasted above and it gives me a #REF! error =INDEX($C$9:$Q$9,, SUMPRODUCT(($C$10:$Q$20=C4)*(COLUMN($C$9:$Q$9)-COLUMN($B$4)))).
I'm not sure that I entirely understand how the function is working as I found it as an example online. I try to break apart the function into smaller functions so that I can see individual results but I'm still at a loss. I do not understand what value I should be putting in the very last Column() function and if that is what is throwing off my values. For example, just looking at the sumproduct portion of my second formula gives me the value 18 (SUMPRODUCT(($C$10:$Q$20=C4)*(COLUMN($C$9:$Q$9)-COLUMN($B$4)))) and the 18 column is past where I've told the formula to look. This is my first ever post so please let me know if I can add additional detail or attach my file if necessary.
UPDATED:
=XLOOKUP(1,IF(ISERROR(MAKEARRAY(1,COLUMNS(C10:Q20),LAMBDA(rows,c,SUM(FILTER(OFFSET(C10:C20,0,c-1),OFFSET(C10:C20,0,c-1)=C4))))),0,1),C9:Q9)
That should work for what you're asking. It first finds the column with the desired date, then extracts the entry in row 9.
(Replace C4 with whatever other cells are needed.)
This should address the issue you mentioned in the comments. Basically my array from MAKEARRAY() was only one row tall and could thus only handle instances where one date was present. Adding sum() fixes that.
If this solves your issue (I think it should), please consider marking this as the accepted answer.
