Excel: Find a key word in a transaction and bring the corresponding nature from a mapping list

user_13714836 0
Viewed 87

Upon some extensive research, I couldn't find any answer to my problem. Here it is. Every month, I breakdown my transactions (from bank statements) according to their nature such as food, shopping and entertainment. So I prepared a key word mapping with natures as follows:

Column A - Column B

  • Walmart - shopping
  • Netlix - entertainment
  • McDonalds - food

in two column in excel (list is pretty long, examples are simplified). Each transaction records includes many words and details of the transaction. What I want is, if a transaction includes the key word of NETFLIX, I need to bring word ENTERTAINMENT as nature next to the transaction column in excel. Example: if transaction is "...June charge of Netflix obtained from credit card 12345..." the key word Netflix is included in the transaction details, then bring ENTERTAINMENT. Had I kept my list short, I would have done it with =if(isnumber(search(... formula but my list is looong.

PS. Just extracting out the key word next to the transaction columnn would be fine, too. As the rest can be done by Vlookup.

I need your help friends.

The following formula didn't work for all transactions:

enter image description here

excel-2010 excel-2007 excel-formula excel
2 Answers

You can use a formula like this:

=INDEX(E:E,AGGREGATE(15,7,ROW($D$2:$D$4)/(ISNUMBER(SEARCH($D$2:$D$4,A2))),1))

Realize that this is an array type formula and will iterate the entire key word list twice every time it is calced. too many of these and the calc will be slow.

To ensure you are finding the full word:

=INDEX(E:E,AGGREGATE(15,7,ROW($D$2:$D$4)/(ISNUMBER(SEARCH(" "&$D$2:$D$4&" "," "&A2&" "))),1))

enter image description here

user_48515900

Try the following setup:

enter image description here

With the following formula in D1:

=INDEX(B:B,MIN(IF(ISNUMBER(FIND(" "&A1:A3&" "," "&C1&" ")),ROW(A1:A3),"")))

Note: This is array entered

user_97581940
Related