3

I have en excel file with expenses(the amount of money spent is in one column), and in next column I have short description that is mostly made of multiple words. I want to "simplify" the description and assign a single word or two to each description, which would be in another column next to it. The problem is that the description is not "unified", for example I can have strings like "business lunch","business dinner at restaurant XXX", "coffee with journalists" etc., and I would like to assign these description "food" label. There are also different categories that follow similar pattern.

My idea was to create another table(on different sheet) - in one column I have keywords like "coffee","lunch","dinner" and in column next to them I labels that I want to have assigned, which is "food". I used vlookup function with approximate match, but it returns me incorrect results. For some reason the order of words in list seem to affect the results, and even though there is partial match(exact in one word of the string) the vlookup ignores it and returns something else. For example I have "parking at hotel xxx" and in table I have "parking"-"travel expenses" pair, the vlookup returns "food" label.

Can you help me solve this problem? (is there different approach that you would suggest?)

2
  • you are misunderstanding how vlookup works. The "closest match" you are referring to will only work correctly with numeric data sorted ascending in the left most column.
    – Tyson
    Jul 19, 2014 at 12:06
  • Just for reference. Here's somebody else with the exact same question, but for Excel. mrexcel.com/forum/excel-questions/…
    – Chris
    Sep 14, 2017 at 6:26

2 Answers 2

10

You want the FIND() and/or SEARCH() function.  Usage:

FIND(find_text, within_text)
                                                                returns the starting position of the first text string
                                                                within the second text string (starting at position 1)

So FIND("lunch", "lunch with customer") returns 1, and FIND("lunch", "business lunch") returns 10.  If the first string is not found in the second, this returns a #VALUE! error value.  SEARCH() is like FIND() except for the fact that FIND() is case-sensitive and SEARCH() is not.  So

FIND("lunch", "Lunch with customer") returns #VALUE!
but
SEARCH("lunch", "Lunch with customer") returns 1

I’ll assume that you’ll want to use SEARCH(), the case-insensitive one.

You’ll want to set up an array like this:

                                                        keyword list

It’s probably better to do this in a separate sheet; let’s call it Key-Sheet.  Then, on your data sheet: If your free-form description is in column A (starting in cell A1), enter the following into cell B1:

=MATCH(MIN(IFERROR(SEARCH('Key-Sheet'!$A$1:$A$7,$A1),LEN($A1)+1)), SEARCH('Key-Sheet'!$A$1:$A$7,$A1))

and press Ctrl+Shift+Enter, to make it an “array formula”.  (It will display in the formula bar in braces.)  Explanation:

  • SEARCH('Key-Sheet'!$A$1:$A$7,$A1) – for each keyword from column A of the key sheet (“coffee”, “lunch”, “dinner”, etc…), search for it in the description in the current row, column A, of the data sheet (e.g., “business lunch”).  This will create an array containing { #VALUE!; 10; #VALUE!; … } (seven elements (in this example), one per keyword; the second one shows the result for “lunch”, which is in 'Key-Sheet'!A2).
  • IFERROR(…,LEN($A1)+1) – replace #VALUE! values with 15, which, being LEN("business lunch")+1, cannot possibly be a valid return value from SEARCH() (and which, in fact, is higher than any possible valid return value from SEARCH()), but which is a valid number.  So now our array is { 15; 10; 15; … }.
  • MIN(…) – extract the minimum value from the array: in this example, 10.  In general, this will be the (first) successful return from SEARCH().
  • =MATCH(…, …) – note that the second parameter to MATCH() is the same as the first bullet, above.  So we are looking for 10 in the array { #VALUE!; 10; #VALUE!; … }.  This returns the position of the 10, which is 2, corresponding to the fact that A1 on the data sheet (“business lunch”) contains “lunch”, which is in the 2nd row of the Key-Sheet.

To get the expense category, it’s a simple matter of indexing into column B of the Key-Sheet.  Set cell C1 to =OFFSET('Key-Sheet'!$B$1,B1-1,0).  (This does not need to be an array formula.)

                                expense data

Note (as foreshadowed above) that, if an expense description contains multiple keywords, this will find only the first one.

If you don’t want to bother with the intermediate value, you can just compute

=OFFSET('Key-Sheet'!$B$1,MATCH(MIN(IFERROR(SEARCH('Key-Sheet'!$A$1:$A$6,$A1),LEN($A1)+1)),SEARCH('Key-Sheet'!$A$1:$A$6,$A1))-1,0)

This does need to be an array formula.


P.S. the FIND() and SEARCH() functions have an optional third argument:

SEARCH(find_text, within_text, [start_num])

So

SEARCH("cigar", "Sometimes a cigar is just a cigar.") returns 13
but
SEARCH("cigar", "Sometimes a cigar is just a cigar.", 17) returns 29

I don’t see any reason for you to use it.

3
  • In the all-in-one formula with OFFSET, is the range spec of $A$1:$A$6 a typo (vs. $A$1:$A$7)? BTW, nice solution.
    – fixer1234
    Apr 23, 2019 at 16:20
  • 1
    thanks for the very detailed answer and nice solution! I found a similar approach using the new XLOOKUP here: exceljet.net/formula/categorize-text-with-keywords =INDEX(categories,MATCH(TRUE,ISNUMBER(SEARCH(keywords,text)),0)) (apply as array function! this will show the curly brackets)
    – Ewout
    Feb 5, 2020 at 21:31
  • Adding a comment in case other Mac users come here... the key combination for "array formula" is still CTRL + SHIFT + RETURN, not CMD as one might think.
    – VBB
    Feb 8, 2023 at 22:43
0

As Tyson said, the "close/approx." match is not meant for words. To quote the help file:

If range_lookup is either TRUE or is omitted, an exact or approximate match is returned. If an exact match is not found, the next largest value that is less than lookup_value is returned.

Which means if you lookup the value "7" in "1,2,5,8,12", the value returned would be "5", which is the closest value to 7 that is not larger than 7.

There is no easy way to do what you want without some kind of extensive programming and evaluation of both individual words and grammar analysis.

What you should do is train yourself to enter some kind of "category code" when you originally enter the data, and then use a memo column for "additional details".. like "01-Food and Drink","Took boss out to dinner for his birthday".

If you already have a large amount of data where this may be difficult to do, you can do a few tricks to speed things up (although you'll still have to do a lot of manual sorting things out)..

Start by adding a column that checks the description for the word "park", and returns 0 if not found, 1 if found.. something like "=If(Search("park",A1)>1,1,0)" (and then auto-copy the formula down all the rows of your data). Then, you can sort the whole table by that column, so your data is split in to two groups: Descriptions with "park" in them, and ones without. Add another column for say, ones with "food" in them. Then, between "food" and "park", you can sort (using both columns) in to four groups: Ones without neither word, ones with "food", ones with "park", and ones with both.

Doing this repeatedly, you can quickly sort out groups which are clearly one category or another, mark them with a category code, and ignore them from then on while doing additional word searches, until everything has been categorized.

1
  • thank you. Yea the problem is that I already have it as it is, with lot of data, but fortunately I found fuzzy lookup add-in from MS, that basically works as I described.
    – cgnx
    Jul 20, 2014 at 8:44

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .