I have the below written formula that looks in the securities! sheet containing stocks and it fetches all stocks that have a dividend rate under the desired value stated in the $B$2 cell. I have the same formula set up to work for when I need to fetch all stocks issued by the same issuer, where !securities$J:$J=$A$2 and of course $A$2 contains the value of the issuer.

ArrayFormula(INDEX(securities!$A:$J,SMALL(IF(securities!$B:$B<=$B$2,ROW(securities!$A:$A)),ROW(1:1)),1))

However, when I try to write a formula that combines both conditions, meaning it should fetch both all stocks under $B$2 dividend rate and issued by the same $A$2 issuer, it returns an error: "Error SMALL has no valid input data.". This is what I tried:

ArrayFormula(INDEX(securities!$A:$J,SMALL(IF(and(securities!$B:$B<=$B$2,securities!$J:$J=$A$2),ROW(securities!$A:$A)),ROW(1:1)),1))

I am clearly missing something and I can't seem to get this to work. Any ideas where should be the problem and how can I fix it?

Here is a link to the Google sheets spreadsheet so you can try: Google Sheets

CodePudding user response：

You're using the complete wrong formulas for these.

Delete Sheet2!A5:C. When you're done, you should have no formulas left in the sheet.

The place the following formulas into your sheet...

In A5:

=IFERROR(FILTER(securities!A:A,REGEXMATCH(securities!A:A,A2)),"No Matches")

In B5:

=IFERROR(FILTER(securities!A:A,securities!B:B<=B2),"No Matches")

In C5:

=IFERROR(FILTER(securities!A:A,REGEXMATCH(securities!A:A,A2),securities!B:B<=B2),"No Matches")

These will each produce all results for their respective columns.