Is it possible to use this index formula on multiple columns?

=index(if(Sheet1!B5:B<>"",,Sheet1!A5:A))

[Sample Table]

For the table above, what if we want to add Column C? (If column B and C have data on Sheet 1 - remove them, and if 1 is missing in either column(B or C), it will appear.

CodePudding user response：

The desired results shown in the screenshot suggest that you want the matching data without inserting blank rows in between. To get that, use filter(), like this:

=filter(Sheet1!A5:A, isblank(Sheet1!B5:B)

To add another criterion, and combine the criteria with OR, use the operator, like this:

=filter(Sheet1!A5:A, isblank(Sheet1!B5:B) isblank(Sheet1!C5:C))

To get an AND condition, simply add criteria as their own parameters, or use the * operator. See