offset and match? question

[ebsmcb]

I'm trying to return the cell contents -1 column away from the first occurrence of a repeating text string... See below for an example. I've tried using various combinations of offset and match to no avail.

sample #		data 1	data 2	data 3	data 4	data 5	etc
1	return contents of orange text here	3	4	5	na	na
2	return contents of orange text here	10	8	na	na	na
3	return contents of orange text here	16	na	na	na	na
etc	etc

 

[ebsmcb]

The problem is the cells do not have na, they have #N/A which is a totally different thing.
Try

=TAKE(FILTER(F2:NA2,NOT(ISNA(F2:NA2))),,-1)

Yep! That totally worked. Many, many thanks.

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[ebsmcb]

This is a weird follow-up question... Is there a reason why the formula above (specifically the 'filter' command) would work on an iOS operating system, but not a PC (Windows v10 enterprise)? We keep getting an error code that says "that function isn't valid" and it highlights the 'filter' function in the formula.

 

[Fluff]

That's because either you are not using 365 on the PC, or it 's not fully updated.