Find first non-blank cell with INDEX/MATCH

Hi guys!

I'm pulling 2 columns, each with multiples of the same value, and some rows have blanks in the cells until the reference sheet is updated with said information. My INDEX/MATCH works great, except there it pulls the blank values, and thereby reducing the accuracy of my sheet.

The formula is as follows; =IF(ISBLANK([Region Name]@row), "", INDEX({Office RP}, MATCH([Region Name]@row, {Office Region}, 0)))

{Office RP} is the column that contains the blank cells on the reference sheet.

A challenge if it does needs to be addressed; I cannot manipulate the reference sheet as that is updated nightly from a SQL connector.

I searched and found some INDEX/COLLECT formulas, and tried, then failed. I was getting #INCORRECT ARGUMENT SET at each iteration.

How can I get the formula to pull in the first non-blank cell value?

Find more posts tagged with

Sheets

Formulas

Accepted answers

Paul Newcome

Try this...

=IF([Region Name]@row <> "", INDEX(COLLECT({Office RP}, {Office RP}, @cell <> "", {Office Region}, @cell = [Region Name]@row), 1))

All comments

Paul Newcome

Try this...

=IF([Region Name]@row <> "", INDEX(COLLECT({Office RP}, {Office RP}, @cell <> "", {Office Region}, @cell = [Region Name]@row), 1))

Faaez Kamaal

Thank you, Paul!

I'm still scratching my head why yours works, and my implementation didn't.

Paul Newcome

Happy to help. 👍️

There are a number of reasons why yours could have been failing. One that I personally do quite frequently is I forget the first range in the COLLECT function. Between using a lot of COUNTIFS and the fact that you have to put the same range in twice it tends to get lost in the shuffle.

..............INDEX(COLLECT({Office RP}, {Office RP}, @cell <> "", {Off..........................

Another one that I do regularly is spend so much time and energy on the COLLECT that I forget to specify that we want row 1 in the INDEX function.

...........INDEX(COLLECT({Office RP}, {Office RP}, @cell <> "", {Office Region}, @cell = [Region Name]@row), 1))

Michael Campbell

Paul, your explanation was as helpful as your solution. Thank you for taking the time. Michael

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.