Need to return the first NON-BLANK value if [Client full name]@row matches {Client full name}

Laura Sorensen

I have a MASTER sheet containing client data, and an UPDATE sheet to use for processing updates to client data (i.e. users submit data updates via a form and the latest entries go to the top of the sheet).

To ensure my master sheet contains the most up-to-date client data, I am trying to use an INDEX/COLLECT function to reference the update sheet. Problem is, some update rows don't have all columns filled out - thus, I need my index/collect formula to find the first non-blank cell that matches my criteria (client name).

In the following example, I'm trying to pull the client gender from the update sheet:

=INDEX(COLLECT({Client Master_Updates GENDER}, {Master_UPDATES Fullname}, [Client full name]@row), 1)

This formula is returning a blank result as the first row in the update table has no value in the "gender" column (it is working fine for entries that have non-blank cells).

Any help would be most appreciated :)

Paul Newcome

Give this a try:

=INDEX(COLLECT({Client Master_Updates GENDER}, {Client Master_Updates GENDER}, @cell <> "", {Master_UPDATES Fullname}, [Client full name]@row), 1)

Paul Newcome

Happy to help. 👍️

And yes. It is pretty much the same thing.

<> is "not equal to" and "" (double quotes) is "blank". So <> "" is saying quite literally "not equal to blank".

Paul Newcome

Give this a try:

=INDEX(COLLECT({Client Master_Updates GENDER}, {Client Master_Updates GENDER}, @cell <> "", {Master_UPDATES Fullname}, [Client full name]@row), 1)

Laura Sorensen

Thank you for your response. That worked great :)

I also got it working using NOT(ISBLANK(@cell) - is this essentially the same thing?

Paul Newcome

Happy to help. 👍️

And yes. It is pretty much the same thing.

<> is "not equal to" and "" (double quotes) is "blank". So <> "" is saying quite literally "not equal to blank".