Find last cell with data, then get column title

[TAPS_MikeDion]

Hi everybody,
This seems like it will probably be an easy one for those of you who are better than I am with Google Sheets formulas.

I have columns R through AC marked JAN, FEB, MAR and so on for the months of the year, with staff rows starting at 3 with a simple X per staff member who attended a meeting that month. All I'm looking to do in column AD is to show the last month that has an X in the cell per staff member.

P​	Q​	R​	S​	T​	U​	V - AC​	AD​
1	MEETINGS HELD	X​	X​	X​			​
2		JAN​	FEB​	MAR​	APR​	ETC​	LAST MEETING ATTENDED​
3	JOE	X​					JAN​
4	BILL		X​	X​			MAR​
5	TOM	X​	X​				FEB​

In the example above, column AD for rows 3, 4, and 5 should read:

3 - JAN
4 - MAR
5 - FEB

Thank you very much for your help on this!

 

[Fluff]

No idea if this works in sheets, but try

=lookup(2,1/(r3:ac3="x"),r$2:ac$2)

 

[TAPS_MikeDion]

Hi Fluff!
Using that formula in cell AD3, I got the following error:

#N/A
Error
Did not find value "2" in LOOKUP evaluation

 

[TAPS_MikeDion]

Thanks to your formula I was able to figure it out looking at how Google Sheets uses LOOKUP.

Here's what worked:
=LOOKUP("x",R3:AC3,R$2:AC$2)

The only issue I have now is figuring out how to tell Google Sheets to ignore an error, because if someone hasn't been to any meetings, I get:

#N/A
Error
Did not find value "x" in LOOKUP evaluation

 

[Fluff]

Try

=IFNA(LOOKUP("x",R3:AC3,R$2:AC$2),"")

 

[TAPS_MikeDion]

And Fluff to rescue again!!!

Thank you, it works like a charm.

 

[Fluff]

Glad to help & thanks for the feedback.

 

[TAPS_MikeDion]

Hello again,
How can I change this formula so that it finds the the farthest right column with data within the (new) range of column D to column O (for the MONTHS - ie JAN ,FEB, MAR...)?

Right now, if a meeting is cancelled, the formula that was working stops at the last meeting month prior to the cancelled meeting.
For example, our monthly meeting for April was cancelled. Anyone who attended the MAR & MAY meetings now show their last meeting as MAR instead of MAY.

Thank you!

 

[Fluff]

As long as there is an "X" for May then the formula should work.

 

[TAPS_MikeDion]

Hi Fluff,
I switched this over to Excel so that I could use VBA and used the original formula you gave me. It DOES work now, with the exception of anyone who hasn't attended a meeting. In that case I receive an N/A error.