Multiple Criteria with IF, ISNUMBER, SEARCH

I've got a sheet with a list of lenders and I'd like to be able to quickly look up specific companies that meet one or more criteria. I have an IF(ISNUMBER(Search function that works pretty well for looking up one criteria, but I'm at a loss for how to add additional search terms.

Here is what I have:

=IF(ISNUMBER(SEARCH(D2,Sheet1!CO9:CO268)),Category:Description,"Not Found")

I'm using named ranges, so this searches for all rows that contain the search term listed in D2 (in this case a state that the bank operates in). If it matches then the whole row is printed (i.e. Category:Description). I've got the formula in B6.

How do I have it search for stuff in B2 & C2 as well as D2?

Oh, and ideally when it comes to a row that doesn't meet the criteria it just skips it instead of printing "Not Found" or "0" or whatever else. Ideally it'd only show the matching rows. Maybe that's a different function, I dunno.

3 Answers

If your table is named Lenders, then you can use the following:

If you have O365 with the FILTER function, you can use:

=FILTER(Lenders, (ISNUMBER(FIND($C$2,Lenders[State])))* (Lenders[Category]=$B$2))

If you do not have the FILTER function, the formula gets longer:

=INDEX(Lenders, AGGREGATE( 15,6,1/((Lenders[Category]=B2)* (ISNUMBER(FIND(C2,Lenders[State])))) *ROW(Lenders)-ROW(Lenders[#Headers])+1,ROW(INDEX($A:$A,1):INDEX($A:$A,COUNTIFS(Lenders[Category],$B$2,Lenders[State],"*"&$C$2&"*")))), SEQUENCE(,COLUMNS(Lenders)))

And you can add more criteria if necessary.

To extract multiple records based on multiple criteria, you need an array (CSE) formula:

How it works:

• Enter this array formula in cell A43, and finish with Ctrl+Shift+Enter, fill across.

  {=IFERROR(INDEX($A$32:$C$37,SMALL(IF(COUNTIF($A$40,$A$32:$A$37)*COUNTIF($B$40,$B$32:$B$37)*COUNTIF($C$32:$C$37,"*"&$C$40&"*"),ROW($A$32:$C$37)-MIN(ROW($A$32:$C$37))+1),ROW(A1)),COLUMN(A1)),"")}

• This part of the formula reads NY which is the part of text string in Column C, works on Wild Card mechanism.

  COUNTIF($C$32:$C$37,"*"&$C$40&"*")

• You may replace Range $A$32:$A$37, $B$32:$B$37 and $C$32:$C$37, with NAMED RANGE.

Adjust cell references in the formula as needed.

One other option, you can reorganize your data alphabetically (or custom) within the various categories (i.e. "Prime Commercial" and then the state, etc) - you can sort your data on up to 4 categories at once. (I do this with my movies, but I list up to 3 subcategories in separate columns - i.e. drama/horror/comedy, rather than within the same column like with the states you show above). Go to the Data tab-->Sort--> this opens a dialog box where you can add levels. Then you can sort (with or without headers) by various columns, a-z or z-a or custom lists like days of the week etc. So as an example in your case, you could first sort the data by 'Category', then 'Lender Name' then 'Contact Person'. Then when you search by state, your results are grouped together as 'Hard Money' first and 'Prime Commercial' second, then within Hard Money, your results will show ABC Lending, then Lending..., then Moose..., etc...