Return the last text value from a multi-column search

Charles Stiegemeier

I have 5 columns from left to right: 'Site 1', 'Site 2', 'Site 3', 'Site 4', 'Site 5'.

I am looking to search each column by row to see if there is a value entered into each site column. If a value is entered, I'd like to return the last site that has a value into a new column labeled 'Latest Site' (e.g. Site 1 and 2 have values so then the Latest Site column returns the value from Site 2).

The site columns would be text fields. Below is an example of what should happen. Could you help with a formula that could be used in the Latest Site column?

Dale Murphy

@Charles Stiegemeier It isn't pretty, but if you are just checking right to left, you could try:

=IF(NOT(ISBLANK([Site 5]@row), [site 5]@row, if(NOT(ISBLANK([site 4]@row, [row 4], IF(NOT( ... ETC ETC

Cheers,

dm

Paul Newcome

Try this:

=INDEX([Site 1]@row:[Site 5]@row, COUNTIFS([Site 1]@row:[Site 5]@row, @cell <> ""))

Genevieve P.

Hi @Charles Stiegemeier

I see you marked both responses as not answering your question. Can you clarify what didn't work for you? Are you getting a formula error or an incorrect result?