Index formula on multiple columns

Question

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.

Kindly add input table and expected output table as **text table** (NOT as IMAGE) to the question. [Click here](https://webapps.stackexchange.com/a/161855/) to create a table easily. Adding such tables greatly increases your chances of getting a elegant answer, as **it is easier to copy/paste**. If you share spreadsheets, your question maybe closed, as questions here must be [self contained](https://meta.stackoverflow.com/a/260455). [Your email address can also be accessed by the public](https://meta.stackoverflow.com/questions/394304/), if you share Google files. — TheMaster, Oct 10 '22 at 04:49
Just add a another nested `IF` or try `IFS`. Related: https://stackoverflow.com/a/46884012/ — TheMaster, Oct 10 '22 at 04:53

score 0 · Answer 1 · answered Oct 10 '22 at 05:41

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 Boolean arithmetic.

score 0 · Answer 2 · answered Oct 10 '22 at 06:28

FILTER() with MMULT() may give you desired result.

 =FILTER(Sheet1!A4:A,MMULT(INDEX(--(Sheet1!B4:C="x")),{1;1})<2)

Here MMULT(INDEX(--(Sheet1!B4:C="x")),{1;1}) will create a vertical array having count how many x do you have in each row. If you have x in both column of each row then MMULT() will return result 2. If you have one x then will return 1 and if there is no x then 0. Then we will filter that vertical array having values less than 2.

MMULT() reference.

Index formula on multiple columns

2 Answers2