Return first non-empty value in a range

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

Find more posts tagged with

Accepted answers

Paul Newcome

Try this...

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

All comments

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. 👍️

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.