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

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 :)


Best Answers


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!