Excel: Filtering Text to Show Rows with Certain Words and Without Certain Words
Say I Have a Table with 3 Columns: I Want to Filter to Only Show Rows That Contain an Animal from the Animal Field, but Not a Place Name in the City Field. in...
Say I have a table with 3 columns:
I want to filter to only show rows that contain an animal from the animal field, but not a place name in the city field.
In this example, I want to only return row 2, because chicken is in column C, but dallas is not in column B.
From what I've read it seems like I may need to use a COUNTIF in a new column(s) and then filter based on the 0/1. But I'm struggling to get it working properly if the column is not an exact match. I need it to still pick up the row if it was "dallas in Chicken" for example (case and order insensitive).
Would including wildcards on either side of each entry in columns B and C help?
I'd prefer not to use Access if possible as I need to manipulate the data further in Excel.
1 Answer
Helper Column solves the issue:
How it works:
- Formula in Cell
D31to find the partial match.
=IFERROR(LOOKUP(1E+100,SEARCH(C$31:C$34,A31),C$31:C$34),"No Match")
N.B. Lookup(1E+100,, find the largest match value wrapped with SEARCH, as a Lookup value input, then it searches for the first value in the Lookup Array from right to left and it ignores text, errors and blanks cells.
- Final Formula in Cell
E31to pull the Row (Column A Value) for the partial match with Animal name.
=IFERROR(VLOOKUP($D31&"*",$A$31:$C$34,1,FALSE),"")
Edited:
In case of resembling words like
Chick,ickorIck, Formula inE31needs little modification.=IFERROR(VLOOKUP($D31&" *",$A$31:$C$35,1,FALSE),"")SPACE before
*sign,,(VLOOKUP($D31&" *",.
- Fill Formulas Down.
- Adjust cell references in the Formula as needed.
Robert Thorne covers electric vehicle innovations, autonomous driving systems, global mobility trends, and automotive engineering developments.
