Solved Find a cell based on another cell excluding blanks

May 8, 2020 at 19:45:48
Specs: Windows 10
I need to find a corresponding date based on the value "x" indicated as present in class. The date is in the header row for each day of the week. If a student is present an x is indicated. If a student is absent the cell is left blank. I am trying to find the last date attended for each student in a class. I am using Google sheets but can use Excel if needed.

See More: Find a cell based on another cell excluding blanks

Reply ↓  Report •

#1
May 9, 2020 at 06:31:06
✔ Best Answer
Assuming your Dates are in B1:H1, and your x's start in Row 2, this should work:

This is an array formula that must be entered using Ctrl-Shift-Enter

=INDIRECT(ADDRESS(1,MAX((B2:H2<>"")*COLUMN(B2:H2))))

To deal with a student who Never Attended, you could Ctrl-Shift-Enter this:

=IFERROR(INDIRECT(ADDRESS(1,MAX((B2:H2<>"")*COLUMN(B2:H2)))),"Never Attended")

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#2
May 9, 2020 at 06:57:45
WOW! Thank you so much! I really appreciate your expertise. It worked perfectly.

Reply ↓  Report •
Related Solutions


Ask Question