Please help me simplify my formula

[MAP]

Firstly, I am a novice working with Excel 2010 and using only formulas. I am working on a worksheet to help do a little bookkeeping. I am using the following formula in Column F as "helper cells" to determine what type of transaction is happening so I can have conditional formatting color the cell. If cell in column A was CREDIT, the value assigned to this column F helper cell would be 1... DEBIT would be 2, CHECK would be 3, 9030 would be 4, 4846 would be 5, and if column A cell was empty, the value would be 0 (zero).

=IF(A35="Credit",1,(IF(A35="debit",2,(IF(A35="check",3,(IF(A35="9030",4,(IF(A35="4846",5,0)))))))))

Can you help me simplify it? I know there has to be a better and easier way to do this.

Also, the numbers "9030" and "4846" are not getting recognized... apparently Excel is expecting a particular type of formatting (maybe text), but I seem to be pasting a number (from what the bank's transaction lists provides)

Excel experts, Please help me to get my formula to work and then to help me simplify it.

A	B	C	D	E	F
Details​	Date	Description	Amount	Type
DEBIT	Thu, Jan 02, 2020​	WELLS BANK	(1,688.64)​	ACH_DEBIT	=IF(A35="Credit",1,(IF(A35="debit",2,(IF(A35="check",3,(IF(A35="9030",4,(IF(A35="4846",5,0)))))))))
CREDIT	Thu, Jan 02, 2020	QuickPay with Zelle payment	1,703.00​	QUICKPAY_CREDIT
CHECK	Mon, Jan 06, 2020​	CHECK 6304	(150.00)​	CHECK_PAID
9030​	Tue, Jul 14, 2020​	WAL-MART #2	(16.02)​	Food & Drink
4846​	Wed, Jul 08, 2020​	EXXONMOBIL 480	(20.00)​	Gas

 

[jasonb75]

Also, the numbers "9030" and "4846" are not getting recognized

It should be 9030 and 4846, not "9080" and "4846". Numbers should not be enclosed in double inverted commas.

Unless you need to add more IF's to the existing formula, your's is the simple version. The 'proper' way would be with a lookup table, but with a small list there is no advantage.

With a stand alone formula you could use this one, but it will probably take longer to calculate than your existing formula.

=IFERROR(MATCH(A35,{"Credit","debit","check",9030,4846},0),0)

For a lookup table you would use almost the same (slower) formula, but with the 5 criteria listed elsewhere in the sheet instead of in the formula.

 

[MAP]

It should be 9030 and 4846, not "9080" and "4846". Numbers should not be enclosed in double inverted commas.

Unless you need to add more IF's to the existing formula, your's is the simple version. The 'proper' way would be with a lookup table, but with a small list there is no advantage.

With a stand alone formula you could use this one, but it will probably take longer to calculate than your existing formula.

=IFERROR(MATCH(A35,{"Credit","debit","check",9030,4846},0),0)

For a lookup table you would use almost the same (slower) formula, but with the 5 criteria listed elsewhere in the sheet instead of in the formula.

Hello jasonb75, i thank you for your explanation. I now realize i should have used the numbers without the quote marks.

If you can explain how your IFERROR(MATCH formula can work, i will be greatly appreciative.

I still hope there will be a simpler solution than my multi-nested IF formula

 

[Fluff]

What version of Excel are you using?
Please update your account details to show this, as it affects what functions you can use. Thanks

 

[jasonb75]

I still hope there will be a simpler solution than my multi-nested IF formula

With excel 2019 / 365 you could use IFS instead of IF

=IFS(A35="Credit",1,A35="debit",2,A35="check",3,A35=9030,4,A35=4846,5,TRUE,0)

Other than that, trying to simplify it will be a false economy, I provided the match formula for you as an example, but for a list as short as yours I would personally stick with IF's or IFS depending on version.

If you can explain how your IFERROR(MATCH formula can work, i will be greatly appreciative.

Excel comes with a help file that tells you how all of the functions work. Type =MATCH into an empty cell, then press the f1 key (works for any function).

 

[Fluff]

with 2019/365 another option
=Switch(A35="Credit",1,"debit",2,"check",3,9030,4,4846,5,0)