How to get different outputs from sets of keywords

**Bitteeinbit** · 10-31-2011, 04:42 PM

So I have a table of roughly 200 internet search keywords from two different companies, which I want to sort. One is Walmart the other is Costco.
So keywords might look something like this:
"Walmart Canada"
"Walmart groceries"
"Walmart"
"Costco Canada"
"Costco"
"Costco bulk prices"
and so on.

In column B, I want to display the following numbers: 1 if it refers to Walmart, 2 if it refers to Costco and 3 if it contain a keyword that refers to BOTH. So in this case it should show:
3 (Canada it common to both)
1
1
3 (Canada is common to both)
2
2

What I'm having trouble with is displaying the 3's. These are different approaches I've been taking:
=IF(ISNA(VLOOKUP("walmart",A3:C103,1,FALSE)),1,IF(ISNA(VLOOKUP("groceries",A3:C103,1,TRUE)),1,IF(ISNA(VLOOKUP("Costco",A3:C103,1,FALSE)),3,2)))

OR

=IF(AND(NOT(ISNA(MATCH(A2,walmart!$A$2:$A$102,0))),NOT(ISNA(MATCH(A2,costco!$A$2:$A$102,0)))),2,IF(NOT(ISNA(MATCH(A2,walmart!$A$2:$A$102,0))),1,IF(NOT(ISNA(MATCH(A2,walmart!$A$2:$A$102,0))),,2)))

In the first case, it works but I can't get 3's to show up, and I need too many words for the formula to work (given that I have so many keywords). I've used to formula with a much greater number of words but given all the words, it won't work.

In the second case, I can get 1's and 2's, but once more the 3's won't show. It's as if "walmart" will take precedence over "canada", so it'll mark it as a 1. I've tried simply with If and vlookup but this is starting to frustrate me.

Thanks in advance for any input.

-B

**ChemistB** · 10-31-2011, 04:46 PM

I'm having trouble determining what a keyword would be that refers to both Walmart and Costco. Would it have both words in it? or neither word?

Possibly
=IF(AND(ISNUMBER(SEARCH("COSTCO",A1)),ISNUMBER(SEARCH("Walmart",A1))),3,IF(ISNUMBER(SEARCH("Walmart",A1)),1,IF(ISNUMBER(SEARCH("COSTCO",A1)),2,3)))

**Bitteeinbit** · 10-31-2011, 05:12 PM

I had actually tried a variation of the formula you just gave (though without AND), but the number of keywords made it difficult to do.
A keyword that refers to both is simply a word that would appear in both categories. So "canada" would be a keyword which appears in both the costo and walmart list. So would many other words such as "groceries", "sale", etc. Problem is: the lists are fairly large and since I took them from the internet searches, some of them are even misspelled (walmaart, costko, etc).

With the example you provided, where would I put the words I deem to fall in the two categories? Nowhewre right? I would have to find all keywords, then the "3" at the end would cover the rest? But what if it says: "Costo Canada"? We have "costco", so would it go under 1 or 3?

**ChemistB** · 11-01-2011, 09:10 AM

The way mine is written, if it contains Costco and Walmart, or neither then it's a 3. If it contains Costco, it's a 2 and if it's Walmart only it is a 1. Based on what you are putting it, you'd need three columns of keywords Both, Costco and Walmart. You'd need to have the first if look at the Both column for a match (and assign a 3 if it finds one) then the other two columns. Does that make sense?