CODEWITHSUNDEEP
excelexcel-formula
Acesofsplades
Acesofsplades

Reputation: 1

Matching Multiple Keywords Exactly in Excel

Im trying to search through 1000s of invoice lines to locate prices paid for services.

Essentially the format of each line is the same going [Size][Container][Material][Process] the below image is an example.

sample image

enter image description here

I just want have a formula where I can select the range, and it will pick up any line item that has 3 keywords, but only those three, if it has 2 of the 3, it will ignore it etc.

So ideally I can then have a list of the lines that match that search function and the prices paid.

I hope someone can help, I've only managed to make it search for keywords in general but not exact matches

Upvotes: 0

Views: 512

Answers (1)

Hakan ERDOGAN
Hakan ERDOGAN

Reputation: 1210

If I understand correctly, you want to quick filter according to 3 conditions at the same time. This is kind of easy using a helper filter column.

Please see the screenshot first: enter image description here

Filter is our helper column, we will try to produce TRUE and FALSE values here according to the keywords you enter into E1, F1 and G1 cells. TRUE means the cell on the left has all those keywords.

The formula of B2 is as follows and should be copied down:

=ISERROR(FIND(E$1,$A2)+FIND(F$1,$A2)+FIND(G$1,$A2))

The formula basically tries to find the location of 3 keywords in our description string and add them up. If one of them is not found, then an error is thrown and the sum will also be an error. I used this error to see whether all of the keywords exist in our string or not.

Finally you should open the filter for COL:A and COL:B and filter TRUE values to see only the rows containing your keywords.

Upvotes: 2

Related Questions