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])))
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:
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 |
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 :
• 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,""))))