Return List of Values That Match One or More Criteria

Question

First time poster here.

I currently have a spreadsheet with 2 columns of data; one with a list of stock tickers (column B), and the other with their corresponding industries (column C). I want to use a formula to return a new list of tickers whose industries match either of the two specified in column J: 'Packaged Software' or 'Information Technology Services.'

In columns E and F, I have created this list according to each criterion separately, which works as intended by entering the formulas below in cells E2 & F2, respectively, and dragging down.

{=IFERROR(INDEX($B:$B,SMALL(IF($C:$C=$J$2,ROW($B:$B)),ROW(1:1)),1,1),"")} {=IFERROR(INDEX($B:$B,SMALL(IF($C:$C=$J$3,ROW($B:$B)),ROW(1:1)),1,1),"")}

However, the issue occurs in column G when I try to combine the two using an IF(OR() statement in cell G2, as shown below.

{=IFERROR(INDEX($B:$B,SMALL(IF(OR($C:$C=$J$2,$C:$C=$J$3),ROW($B:$B)),ROW(2:2)),1,1),"")}

As you can see in the first image, this formula returns the entire list of companies rather than just those that fit either of the two criteria. I have reviewed many similar examples but none seem to solve the persistent problem I am encountering, including an old question on this forum titled, "Return list of all values that match any of a few criteria". I adapted this formula from that post and have played around with it thoroughly, but I cannot seem to get it to work properly in my spreadsheet for some reason.

If anyone can offer assistance, it would be greatly appreciated; thank you.

Answer 1

If you have the recent upgrades to Excel, SPILL functionality and the functions introduced with it, the following formula should work for you:

=SORT(UNIQUE(IF(C2:C9=J2,B2:B9,IF(C2:C9=J3,B2:B9,"")),,1))

It will limit the list to the criteria you specify. You can lengthen the list by adding extra IF()'s in the nesting chain. UNIQUE() will limit it to just the successful matches and SORT() puts them in order. SPILL means you just need the single cell formula, no choosing how many cells to put the formula into. Naturally, the ranges shown would need adjusted for your actual used rows.

In your post you use the {CSE} entry method, hence, probably, Ron Rosenfeld's question but perhaps that is just habit. If not, the following {CSE} formula will give you the list of matches, but also however many blank entries:

{=IF(C2:C9=J2,B2:B9,IF(C2:C9=J3,B2:B9,""))}

You can then collect them and drop the blank cells however works for your usage.

A different approach that can be the basic data collector is to use IFS() instead of the nesting chain of IF()'s. Not only do you not run into nesting limits, but you can build the "test/result" pairs outside of the cell and use string building techniques to put it all together. This will replace the IF() chain:

=IFS(C2:C9=J2,B2:B9,C2:C9=J3,B2:B9,TRUE,"")

or {CSE}'ed as well.

Interestingly, fitting the individual tests into an OR() doesn't work as the function collapses the array of results into a single result, even with SPILL functionality. Not too handy for building a list... There is usually a way to get the result array back, weirdly enough, by wrapping the function that collapses it in one that is happy to use the array. "Weirdly" because you figure when a function takes only the first result it has discarded the rest of the array, but it couldn't have if a function wrapping it can STILL use the array because the rest of it would have been gone. That suggests even when it seems collapsed, the whole array still exists, that Excel keeps it until the formula finishes. Wish I could think of a way to use that outside the obvious.

Point is, you might be able to find the key and use OR(), but if you don't like nesting IF() chains, using IFS() is pretty darn easy, easy as using OR() so why bother... so I didn't bother...