Return first non-empty value in a range

Danny Frank

Hello everyone,

I am attempting to write a formula that will search through row data and return the first non-empty value it finds. In the example below, the cell for city is where the formula will be placed. It should scan the rows [AZ Cities]@row:[CA Cities]@row and return Yorba Linda.

If the state was AZ, The [CA Cities] row would be blank and the formula might return Phoenix.

Appreciate any help you might be able to provide.

Thank you

Paul Newcome

Try this...

=INDEX(COLLECT([AZ Cities]@row:[CA Cities]@row, [AZ Cities]@row:[CA Cities]@row, @cell <> ""), 1, 1)

Paul Newcome

Try this...

=INDEX(COLLECT([AZ Cities]@row:[CA Cities]@row, [AZ Cities]@row:[CA Cities]@row, @cell <> ""), 1, 1)

Danny Frank

You're a wizard, Paul!

Thank you so much, that worked beautifully

Paul Newcome

Happy to help. 👍️