Index and Match to return match with non-blank cell

KelliBretsch

We have product lists, sometimes the description for a product is 20+ lines long. I'm trying to get the important items, such as fire rating to float to the top of those 20 rows.

In the example below all of the rows highlighted in yellow are for one product. I'd like to get the Fire Rating data to pull into the same row as the Cost Line Item. Index and match is only returning the first cell in the fire rating column, which is blank. I'd like to use Index and match with COUNTA, but that isn't an option in smart sheet.

Any help would be greatly appreciated.

Paul Newcome

Try an INDEX/COLLECT. It will allow you to specify the matching criteria as well as the non-blank criteria.

=INDEX([Pull Column]:[Pull Column], [Pull Column]:[Pull Column], @cell <> "", [Match Column]:[Match Column], "match criteria"), 1)

KelliBretsch

Thank you so much Paul!

Paul Newcome

Happy to help. 👍️