Apple Numbers: How to find the first non-blank value in a specific column of all rows belonging to the same category?

Question

Level 1

4 points

Apple Numbers: How to find the first non-blank value in a specific column of all rows belonging to the same category?

Disclaimer: I am not very experienced with Numbers, so be gentle ;)

In the table below, for all cells in yellow, I would like to find the value of the first (in fact, only) non-blank cell in column A in each category. So, cells B3-B6 should all show "Option 1", and cells B8-B13 should all show "Option 2".

I would like to do this dynamically, that is, for every category in the spreadsheet, simply copying the appropriate formula from, say, cell B3 and pasting it into all column B cells in the new category should automatically retrieve the correct Option value.

I can "hardwire" it for cells B3-B6 with a simple "=$A$2 and equivalently for other categories, but that gets old very quickly when there are many categories.

I have been messing around with INDEX, MATCH, LOOKUP, etc. but was not able to get a solution.

Any help greatly appreciated!

Posted on Feb 10, 2025 5:35 AM

Reply

Answer 1

Camelot

Level 9

58,299 points

Feb 10, 2025 12:32 PM in response to xvimbi

First, I don't understand your table layout since you seem to have unlabelled rows (e.g. between 1 & 2, and 6 & 7), as well as unlabelled columns (e.g. before A), but I'll pass that off as a display issue...

Ultimately there are a few ways of doing this. The 'right' way depends a lot on how fluid and dynamic your data is, but the simplest approach is probably a simple IF() statement.

For cell B3, set the formula to:

=IF(ISBLANK($A2),B2,$A2)

and fill down.

The idea here is that cell B3 checks the value of cell A2 (one row up and one column to the left). If that is blank, it simply copies the value from above. If the IFBLANK() returns false, we have a new category heading, so we use that value instead.

Reply