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 D31 to 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 E31 to 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, ick or Ick, Formula in E31 needs 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.