0

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")

enter image description here

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.

Improve this question
0

3 Answers 3

Reset to default
0

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.

Improve this answer
5
  • Hmm, I might not be understanding your notation. Should I be typing "Lenders[State]" or is some or all of that a place holder for me to add my named range? I've tried it a few different ways and I can't get it to work. I've got the filter function. It either gives me a #VALUE error or doesn't think its a formula at all.
    – OzzyKP
    Commented Sep 24, 2020 at 1:40
  • The notation is based on your data being in a Table named Lenders (as I wrote in my answer) and is called Structured References (you can do an Internet search for more details). You can replace it with regular addressing if you want. Lenders[State] for example would be equivalent to G6:G11 on your screenshot. Lenders would be equivalent to B5:G11 on your screenshot. Because it provides for more understandable syntax, and auto-adjusting of the named ranges as you edit the table, I prefer using Tables, but you don't need to.
    – Ron Rosenfeld
    Commented Sep 24, 2020 at 1:54
  • I got it working! Hooray! Works like a charm, thank you! Now I just need to hope my client has Office365. Oh, another question though, how do you have a criteria be optional? Sometimes they'll search for 3 things, sometimes just one.
    – OzzyKP
    Commented Sep 24, 2020 at 2:02
  • Even if he doesn't, you can use the second formula. For optional criteria, you need to somehow set your formula to return TRUE if nothing is specified. If you can't figure it out, you should create another question with specifics as to what you have tried and include a text table of sample data illustrating your problem. (Not enough information in what you have provided to give a good answer to that question at this time).
    – Ron Rosenfeld
    Commented Sep 24, 2020 at 2:19
  • Since my answer seems to be satisfy your question, I would appreciate it if you would mark it as Accepted. See What should I do when someone answers my question.
    – Ron Rosenfeld
    Commented Sep 24, 2020 at 2:20
0

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

enter image description here

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.

Improve this answer
0

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...

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .