Autofill based on list and value of a cell
I'm making a spreadsheet to help me with my personal accounting. I'm trying to create a formula in LibreOffice Calc that will search in a given cell for a number of different text strings and if found return a text string.
For example, the formula should search for "burger" or "McDonalds" in $C6 and likewise then return "Food" to $E6. It should not be case sensitive. And needs partially to match strings as well as in the case of Burger King. I need it to be able to search for other keywords and return those values as well, like "AutoZone" and return "Auto" and NewEgg and return "Electronics".
I've had a tough time finding any kind of solution to this and the closet I could get was with a MATCH formula but once I nested it in an IF it would not work. I've also tried nested IF with OR; not joy on either.
Examples:
=IF(OR(D10="*hulu*",D10="*netflix*",D10="*movie*",D10="*theature*",D10="*stadium*",D10="*google*music*")=1,"Entertainment",IF(OR(D10="*taco*",D10="*burger*",D10="*mcdonald*",D10="*dq*",D10="*tokyo*",D10="*wendy*",D10="*cafe*",D10="*wing*",D10="*tropical*",D10="*kfc*",D10="*olive*",D10="*caesar*",D10="*costa*vida*",D10="*Carl*",D10="*in*n*out*",D10="*golden*corral*",D10="*nija*",D10="*arby*",D10="*Domino*",D10="*Subway*",D10="*Iggy*",D10="*Pizza*Hut*",D10="*Rumbi*",D10="*Custard*",D10="*Jimmy*")=1,"Food",IF(OR(D10="*autozone*",D10="*Napa*",D10="*OREILLY*")=1,"AUTO","-")))
I can create a different table and make a lookup reference so another way to put this is I need something that does the opposite of what VLOOKUP and HLOOKUP do and return the header value for any data matching in given columns.
Something like:
=IF(NOT(ISNA(MATCH(A1,B3:B99))),B2,IF(NOT(ISNA(MATCH(A1,C3:C99))),c2,0))
If A1 was the test and B2 and C2 were the headers and it was searching below those.
As per my comments, try this:
=IF(SUM(LEN(G150)-LEN(SUBSTITUTE(LOWER(G150),{"hulu","netflix","movie","theater"," stadium"},"")))>0,"Entertainment",IF(SUM(LEN(G150)-LEN(SUBSTITUTE(LOWER(G150),{"burger","taco","vida","cafe","wing","dairy","mcdonald","wendy","kfc","pizza","carl","domino","ceaser","olive","jimmy","custard","subway","arby"},"")))>0,"Food",IF(SUM(LEN(G150)-LEN(SUBSTITUTE(LOWER(G150),{"autozone","Napa","oreilly"},"")))>0,"AUTO","-")))
It is an Array formula and must be confirmed with Ctrl-Shift-Enter.
