Minimize this Forumula

[DjembeDog]

Greetings all!

I began work on a customized home-budget and the below formula quickly got out of control.

(without any context) Anyone have any thoughts on how I might minimize this?

Thanks in advance!


=IF(ISNUMBER(SEARCH("World Vision",G15)),"TITHE: World Vision",IF(ISNUMBER(SEARCH("PEDERNALES",G15)),"UTILITIES: Electricity",IF(ISNUMBER(SEARCH("ATT Payment",G15)),"UTILITIES: AT&T",IF(ISNUMBER(SEARCH("HMLIC/ALIC INS PREM",G15)),"HEALTH: Insurance",IF(ISNUMBER(SEARCH("JPMorgan Chase",G15)),"DEBT: Mortgage",IF(ISNUMBER(SEARCH("AMERICAN INNOVAT",G15)),"INCOME: A.I.",IF(ISNUMBER(SEARCH("OVERDRAFT",G15)),"DEBT: Overdraft",IF(ISNUMBER(SEARCH("GM CARD",G15)),"DEBT: Credit-card payments",IF(ISNUMBER(SEARCH("1ST NATL BK OMAH",G15)),"DEBT: Credit-card payments",IF(ISNUMBER(SEARCH("ATMOS ENERGY",G15)),"UTILITIES: GAS",IF(ISNUMBER(SEARCH("BANK OF AMERICA",G15)),"DEBT: Credit-card payments",IF(ISNUMBER(SEARCH("TXTAG",G15)),"CAR: Tx-Tag ",IF(ISNUMBER(SEARCH("STATE FARM",G15)),"CAR: Insurance",IF(ISNUMBER(SEARCH("ATT",G15)),"UTILITIES: AT&T",IF(ISNUMBER(SEARCH("BOUNTIFUL BASKETS",G15)),"FOOD: Groceries",IF(ISNUMBER(SEARCH("DEPOSIT",G15)),"INCOME: Other",IF(ISNUMBER(SEARCH("PEDIAT",G15)),"HEALTH: Office Visits",IF(ISNUMBER(SEARCH("NETFLIX.COM",G15)),"FAMILY: Entertainment",IF(ISNUMBER(SEARCH("- HEB",G15)),"FOOD: Groceries",IF(ISNUMBER(SEARCH("STICKER",G15)),"CAR: Inspection",IF(ISNUMBER(SEARCH("RANDALLS",G15)),"FOOD: Groceries",IF(ISNUMBER(SEARCH("ROCK MEDICAL",G15)),"DEBT: Medical",IF(ISNUMBER(SEARCH("TAKE SHAPE FOR",G15)),"FOOD: Medifast",IF(ISNUMBER(SEARCH("CITY OF CEDAR PARK",G15)),"UTILITIES: Water",IF(ISNUMBER(SEARCH("CARDIOVASCULAR",G15)),"DEBT: Medical",IF(ISNUMBER(SEARCH("CHILAQUILES",G15)),"FOOD: Dining Out",IF(ISNUMBER(SEARCH("SUBWAY",G15)),"FOOD: Dining Out",IF(ISNUMBER(SEARCH("CHILI'S",G15)),"FOOD: Dining Out",IF(ISNUMBER(SEARCH("NAGOYA",G15)),"FOOD: Dining Out",IF(ISNUMBER(SEARCH("HOT WOK",G15)),"FOOD: Dining Out",IF(ISNUMBER(SEARCH("DONUT",G15)),"FOOD: Dining Out",IF(ISNUMBER(SEARCH("PHUNG",G15)),"FOOD: Dining Out",IF(ISNUMBER(SEARCH("STARBUCKS",G15)),"FOOD: Dining Out",IF(ISNUMBER(SEARCH("CARINO'S",G15)),"FOOD: Dining Out",IF(ISNUMBER(SEARCH("PIZZA",G15)),"FOOD: Dining Out",IF(ISNUMBER(SEARCH("PLUCKERS",G15)),"FOOD: Dining Out",IF(ISNUMBER(SEARCH("PANERA",G15)),"FOOD: Dining Out",IF(ISNUMBER(SEARCH("CORONA",G15)),"FOOD: Dining Out",IF(ISNUMBER(SEARCH("TEXAS LAND",G15)),"FOOD: Dining Out",IF(ISNUMBER(SEARCH("SONIC",G15)),"FOOD: Dining Out",IF(ISNUMBER(SEARCH("OSAKA",G15)),"FOOD: Dining Out",IF(ISNUMBER(SEARCH("MCDONALD",G15)),"FOOD: Dining Out",IF(ISNUMBER(SEARCH("EINSTEIN",G15)),"FOOD: Dining Out",IF(ISNUMBER(SEARCH("TWIN LION",G15)),"FOOD: Dining Out",IF(ISNUMBER(SEARCH("OLIVE GAR",G15)),"FOOD: Dining Out",IF(ISNUMBER(SEARCH("******* BARR",G15)),"FOOD: Dining Out",IF(ISNUMBER(SEARCH("KOBE",G15)),"FOOD: Dining Out",IF(ISNUMBER(SEARCH("BERRY COOL",G15)),"FOOD: Dining Out",IF(ISNUMBER(SEARCH("MANSUN",G15)),"FOOD: Dining Out",IF(ISNUMBER(SEARCH("WENDY'S",G15)),"FOOD: Dining Out",IF(ISNUMBER(SEARCH("CICI",G15)),"FOOD: Dining Out",IF(ISNUMBER(SEARCH("ROSA'S",G15)),"FOOD: Dining Out",IF(ISNUMBER(SEARCH("EXXON",G15)),"CAR: Gasoline",IF(ISNUMBER(SEARCH("SHELL",G15)),"CAR: Gasoline",IF(ISNUMBER(SEARCH("VALERO",G15)),"CAR: Gasoline",IF(ISNUMBER(SEARCH("TEXACO",G15)),"GAS: Gasoline",IF(ISNUMBER(SEARCH("CHEVRON",G15)),"CAR: Gasoline",IF(ISNUMBER(SEARCH("1430 CYPRESS CR",G15)),"CAR: Gasoline",IF(ISNUMBER(SEARCH("AutoZone",G15)),"CAR: Repairs",IF(ISNUMBER(SEARCH("IKEA",G15)),"HOME: Furnishings",IF(ISNUMBER(SEARCH("CVS",G15)),"HEALTH: Prescriptions",IF(ISNUMBER(SEARCH("HOME DEPOT",G15)),"HOME: Repairs",IF(ISNUMBER(SEARCH("LOWE",G15)),"HOME: Repairs","")))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))

 

[Boller]

Prepare a list of the words to search for and the corresponding words to return, and use a lookup formula.

 

[DjembeDog]

Thank you, Boller.

Not well versed in lookup formulas I'm afraid. I've been trying but failing in my efforts.

What I think I do understand: I need a lookup function where if A:A contains the list of C:C, then the associated in D:D appears in B:B.

	A	B	C	D
1	Transaction Description	Results	If:	Then:
2	CHECK CRD PURCHASE 12/30 HOMEDEPOT 1234567?MCC=4567	formula here	HOMEDEPOT	HOME: Repairs
3	CHECK CRD PURCHASE 12/24 CITYELECTRICCOMP 1234567?MCC=9876	formula here	CITYELECTRIC	UTILITY: Electric
4	1ST NATL BK ONLINE PMT 0123456 D0C1F12121212	formula here	1ST NATIONAL BK	DEBT: Credit Card

<tbody>
</tbody>

 

[Aladin Akyurek]

Try something like...

=LOOKUP(9.99999999999999E+307,SEARCH(KeywordList,A2),ResultList)

 

[DjembeDog]

Hm. So, Aladin, would that look like: =LOOKUP(9.99999999999999E+307,SEARCH(C:C,A2),D:D)

 

[Aladin Akyurek]

Hm. So, Aladin, would that look like: =LOOKUP(9.99999999999999E+307,SEARCH(C:C,A2),D:D)

How many words like homedepot that you want to search thru an entry like

CHECK CRD PURCHASE 12/30 HOMEDEPOT 1234567?MCC=4567?

 

[DjembeDog]

How many words like homedepot that you want to search thru an entry like

CHECK CRD PURCHASE 12/30 HOMEDEPOT 1234567?MCC=4567?

Not sure I understand your question.

 

[Aladin Akyurek]

Not sure I understand your question.

Create a 2-column table on a separate sheet, say in A:B like below:

A2: homedepot
B2: repairs
A3: cityelectric
B3: electric
etc.

Name the range in column A KeywordList, the range in B ValueList.

In B2 of your data sheet enter:

=LOOKUP(9.99999999999999E+307,SEARCH(KeywordList,A2),ValueList)

where A2 houses a value like:

CHECK CRD PURCHASE...

 

[DjembeDog]

Sorry, those instructions don't work on a sheet.

Could you clarify please?

 

[Aladin Akyurek]

Sorry, those instructions don't work on a sheet.

Could you clarify please?

See:

https://www.dropbox.com/s/18cwc48923rr9ya/DjembeDog.xlsx