Why does Google sheets return #N/A for MATCH but not for INDEX MATCH-CodePudding

I have a table with several columns, where I'd like to select one row based on the content of several cells. So this is an application of the MATCH function on multiple criteria. This uses the tip explained for instance here https://ask.libreoffice.org/t/multiple-column-match-and-seek-output-value/41966.

An example table:

    A    B    C
1   foo  10   FOO10
2   foo  20   FOO20
3   bar  10   BAR10
4   bar  20   BAR20
5   bar  10   BAR10

So, if I use the formula

=index(C:C,match(1,(A:A="bar")*(B:B=20),0))

It works flawless, and I get BAR20 as expected.

However if I only want to get the index, I thought I could do this by simply removing the index() function. However when I do this, I get #N/A from Google Sheets.

The formula

=match(1,(A:A="bar")*(B:B=20),0)

returns #N/A, with error msg Did not find value '1' in MATCH evaluation.

Why is that? How come it works in the index() function, but not as standalone? I would expect that if a parameter returns #N/A, the function would also return #N/A.

Also, as side question, how can we fix that?

Strangely the same example works in Libre Office Calc.

CodePudding user response：

The reason it doesn't work it's because (A:A="bar")*(B:B=20) is an array formula, which means that in order to work properly it has to be wrapped in a function that enables arrays.

If you do the following you'll see that the formula returns the expected result:

=ArrayFormula(match(1,(A:A="bar")*(B:B=20),0))

Some of the functions that enable arrays are:

ARRAYFORMULA (This function is designed exactly for this)
INDEX (This is the reason INDEX(your_formula) worked)
SORT
SORTN
FILTER
SUMPRODUCT