How to invert INDEX formula in Google sheets

0
votes

I have a sheet named NYSEDB with stock names in column A and the companies that issued them in column B.

In another sheet I have companies in cells B2 and C2 (for example, AAPL, GOOGL etc.).

I have the following formula:

=ArrayFormula(IFERROR(INDEX(NYSEDB!$A:$B,SMALL(IF(NYSEDB!$B:$B={$B$2,$C$2},ROW(NYSEDB!$A:$A)),ROW(1:1)),1,1),""))

This formula, when I spread it down the sheet, returns all the stocks from NYSEDB that are issued by the companies I have specified in B2 and C2.

How can I make the exact opposite formula and "invert" the task - I want for the formula to show all stocks from NYSEDB WITHOUT those issued by the companies I have specified in B2 and C2.

1
google-sheetsgoogle-sheets-formula

1 Answers

1
votes

Solution

Use the not equal operand <> instead of the equal one =.

In this case It's better to use the QUERY function. Which is effectively more expressive than an IF statement and makes exclusion straightforward using an array of exceptions.

Your formula will be then like:

=QUERY(NYSEDB!A:B, "select A where B<>'"&TEXTJOIN("' and B<>'", TRUE, B2:C2)&"'", -1)

Note: you can add as many exceptions as you want, but you will have to edit the B2:C2 range in the QUERY formula.

In this way the Query function will return the column A value whenever the column B value is not included in the exception row.

Reference

QUERY Formula