From column O I would like to lookup column A, starting from my current row, to find the first cell with a comma. Goal is to have the correct date in each row. Table I'm working in https://i.imgur.com/BByfjzy.png

=MATCH("*"&","&"*",$A$1:INDIRECT("A" & ROW()),0)

If I could just run it backwards that be great but I'm not finding a way that works with wildcards or contains in excel 2010. My other thought was to make an a range based off position, invert it, find the index and do length - index but I'm not sure how I would go about that. I'm pretty new to excel so any help would be apricated.

CodePudding user response：

=MAX(IF(ISNUMBER(FIND(",",A1:INDEX(A:A,ROW()))),ROW(A1:INDEX(A:A,ROW())),))

Instead of MATCH which looks from top to bottom and returns the first match, use MAX to return the max row number of the cell containing ,. You can use either FIND or SEARCH.

If you wrap it in INDEX you get your value:

=INDEX(A:A,MAX(IF(ISNUMBER(FIND(",",A1:INDEX(A:A,ROW()))),ROW(A1:INDEX(A:A,ROW())),)))

It might require to be entered with ctrl shift enter. I'm unable to test it in older Excel version.

Edit for further explanation of how it works:

A1:INDEX(A:A,ROW()) is to be read as cell A1 up to the current row in column A. So if you're at row # 10 it would equal A1:A10. Wrapping that range in FIND returns the position of the character you try to find. If given character is not found in the cell it returns error #N/A.

So if you have row 1 and 9 containing , in this case, it returns an array of numbers for the hits and errors for the non-hits, for instance {2,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,6,#N/A} Wrapping that in ISNUMBER changes the non errors to TRUE and the errors to FALSE. IF takes that array and in case of TRUE (a number) it returns the row number (same indexed range is used). Then MAX returns the largest row number of that array.

Instead of FIND you could also use SEARCH. FIND is case sensitive, and SEARCH isn't, further on they operate the same).