excelfilterexcel-formula

Filter Function on list

I have a table that I am trying to use the filter function to report the names based on last names and the priority level. Currently I am able to use the filter function to look at a specific name in a drop down but it does not do it based on priority.

The list currently has 59 values and I am trying to cut down the time it takes and create an easier way to look based on the priority levels. I do have other criteria it is looking (date ranges attached to the names). I am not able to manipulate the data on Table1 due to legalities.

Right now I am using this filter formula:

=FILTER(Table1[Names],ISNUMBER(SEARCH(I1,Table1[Names])))

enter image description here

I'm not sure if the filter function is the best tool or if there is a better suggestion, my VBA usage is very restricted.

This is the formula close to what I am trying to get working on a sample set of data:

=FILTER(Table1[Names],ISNUMBER(SEARCH(Table2[Last],Table1[Names]))*(Table2[Priority]=I1))

So if I wanted to input "High" in I1 it would return as such:

enter image description here

Table1:

Names
Wanda Hayden
Olin Valentine
Harriet Gonzales
Wilfredo Shah
Deshawn Levy
Tanner Avila
Keneth Howe
Chung Hayes
Glenda Cardenas
Laverne Briggs
Jonah Potter
Lyle Gregory
Micheal Reyes
Al Hunter
Normand Bowers
Brock Patrick
Royal Arellano
Ernestine Henry
Armand Arias

Table2:

First Last Priority
Brock Patrick High
Tanner Avila Low
Wilfredo Shah High
Chung Hayes High
Olin Valentine Low
Lyle Gregory High
Deshawn Levy Low

Output Based on High (Cell I1)

High
Wilfredo Shah
Chung Hayes
Lyle Gregory
Brock Patrick
Solution

  • There is nothing wrong with your formula, but in the SEARCH() function, find_text and within_text both are vertical, one or the other needs to be either vertical or horizontal, one other reason has already been mentioned by @ScottCraner Sir :

    enter image description here

    • One alternative using BYROW():

    =FILTER(Table1[Names],
 BYROW(1-ISERR(SEARCH(TOROW(FILTER(Table2[Last],I1=Table2[Priority],"")),Table1[Names])),LAMBDA(α,OR(α))))

    • Or, without using LAMBDA() helper function:

    =CHOOSEROWS(Table1[Names],
SORT(XMATCH("*"&FILTER(Table2[Last],Table2[Priority]=I1),
Table1[Names],2)))

    Or,

    =FILTER(Table1[Names],
 IFNA(SIGN(XLOOKUP(Table1[Names],
 Table2[First]&" "&Table2[Last],Table2[Priority])=I1),0))

    There are many ways of doing this, and since you are specifically looking for the Last_Names in the Names Column, adding another alternative:

    =FILTER(Table1[Names],
 ISNUMBER(XMATCH(TEXTAFTER(Table1[Names]," ",-1),
 FILTER(Table2[Last],Table2[Priority]=I1,""))))