Vlookup. IF a word from a list is in the string of a cell

[bakedziti]

I'm trying to make an easier way to categorize my spending in excel. You know how the bank describes a purchase as random things like "MERCHANT PAYMENT CITY FRESH MARKE - 5ON 010622 FROM CARD#: XXXXXXXXXXXX464X" and "MERCHANT PAYMENT - JEWEL OSCO ON 011022 FROM CARD#: XXXXXXXXXXXX464X"?

So I want to have a list that says IF the string in cell ?? contains "JEWEL", it should return a string of "Groceries". And if it contains "CITY FRESH", it should be "Bakery". Etc.

I uploaded an example to https://docs.google.com/spreadsheets...it?usp=sharing.

[alansidman]

A nested formula will become very convoluted if you have more than a half dozen criteria. I suggest that a VBA solution may be more realistic. How many criteria do you have for this project and do you anticipate having more? If you are open to a VBA solution, then post back and I will offer one up.

[bakedziti]

Thanks Alan!

I'm open to any solutions. I was planning on keeping this in google sheets, but would happily use anything.

With a VBA solution, I can save it to that sheet and it will continue to function, correct?

[bakedziti]

Sorry - to answer the question, I have many and I plan on adding more as I see repeat vendors that I buy from.

[nick.williams]

If your list would be unique then you can use a reference table as per the other thread you posted in. e.g. in D2 of your sample sheet:

=IFERROR(INDEX($J$2:$J$6,MATCH(1,--ISNUMBER(SEARCH($I$2:$I$6,B2)),0)),"")

Note that this needs exact matches from your lookup table, in your sample you have "Fresh Market" in the lookup but the description is truncated as "FRESH MARKE" so it doesn't match.

Also this doesn't deal with anything that meets multiple criteria; it will just categorise as the first one in your table that is triggered.

[bakedziti]

Thank you.

What I ended up using is;

=VLOOKUP(LOOKUP(2^15,SEARCH(Sheet1!$A$2:$A$41,B4),Sheet1!$A$2:$A$41),Sheet1!A:B,2,0)