Auto Categorization with MATCH Formula

[afriedman]

I am trying to add a wildcard to this formula:

Right now, its only looking for the EXACT keyword. I am looking to find variations of the words, so in essence, add in a wildcard (probably associate with Column 'I'.

Attached is the spreadsheet. But to explain what I am looking to do is:

IF 'A2' has any of the words found in column 'I', then return the corresponding cell in Column 'J' (and keep in mind, this list will be a lot bigger. This is just a sample to give an idea of what I am looking to do).

Thanks!

(also, I know this sounds similar to other posts, but this is the product of those, so this is NOT duplicate, Thanks!)

[Sadath31]

try this array formula
=LOOKUP(1,(MATCH("*"&$I$2:$I$41&"*",A2,0)),$J$2:$J$41)

[NBVC]

Try:

=LOOKUP(9.999999E+307,SEARCH($I$1:$I$40,$A2),$J$1:$J$40)

this is a regularly entered formula.

[afriedman]

Sadath31 - Awesome! Works like a charm.

NBVC - I will try thjat too, but what are those numbers you put in at the front of the formula? I am not sure I get how that works...?

[DonkeyOte]

NBVC ... I am not sure I get how that works...?

http://www.excelforum.com/2469516-post4.html

Irrespective of which approach you adopt you might want to consider appending both criteria and Col I strings - adding leading/trailing space (if appropriate) so as to minimise risk of false positive matches

[afriedman]

DonkeyOte,

You raise a good point. How can I avoid the issues where it may fall into more than one category....?

Maybe set up another column next to it indicating how many categories the keyword could potentially fall into? and then manually editing?!?! (Oh no he didnt say manual! )

Any ideas how to automate that further?

[afriedman]

So here is a more practical example of where I am seeing the problem:
(See attached and highlighted cells) I also indicated both formulas you both told me to use.

1. new jersey shouldn't be coming up as location
2. and nj should but isnt.

It sounds like this will be a problem throughout the way I have it set up now...

any thoughts?

[afriedman]

By the way, anyone can feel free to answer this or throw in a thought I really dont want this to get buried

[NBVC]

Since you have state abbreviations in your list, it is picking up the word "in" in any phrase and deducing it to be the state IN. But those phrases can have both the word "in" and an actual state abbreviation like "nj"... so I would suggest splitting the lookup range into two ranges, the locations being separated..

Also, it seems that your lookup criteria column already includes a leading and trailing space... and this is what DonkeyOte was speaking about incorporating into your formula to reduce risk of inaccurate results...

so, since you have done half of that in the table already, then formula will include the "extra space" in the search value (column A).

Try this formula:

Please Login or Register  to view this content.

This formula "chooses" between 3 options. (it kind of works backwards... If none of the lookups are successful, it will return a blank instead of #N/A error, if the last formula is successful it will return that result, else it will look at the first Lookup and return that result, else it will return blank.

[afriedman]

Neat! I assume, I can continue to add to this taxonomy and just change the referenced cells.

But one thing I am not really clear on, what does 'REPT("z",255),CHOOSE({1,2,3},"", 'do?

[NBVC]

That is a technique used to perform an error check.

The Lookup(Rept("z",255)... works just like the Lookup(9.99999e+307.. that we already explained. The difference is the former is used when dealing with textual data and the latter is for numeric data.

So the former looks for the last text entry that is smaller than or equal to a string of 255 z's (which obviously won't be found, so it returns the last text entry).

The Choose({1,2,3},"",Lookup1,Lookup2) returns an array of a blank, and up to 2 text results or 2 #N/A results... most likely it will be one of each in this case.

The Lookup function ignores the #N/A errors and so returns the last item in the array of 3.. e.g. if the result of the CHOOSE({1,2,3} function is {"",#N/A,"location"}, then the LOOKUP(Rept().... returns "location" if the array is {"","services",#N/A} then "services" is returned, if the array is {"",#N/A,#N/A} then the "" (null) is returned

Hope that helps.. kind of in a hurry... sorry...