if statement wilh more than 12 logical formulas

[still learning]

Hi,

I’m writing an If statement and was wondering if I can shorten it.

This is what I got

=IF(OR(A1="abc",A1="def",A1="ghi"),"jkl",IF(OR(A1="mno",A1="pqr"),"stu",""))

I will have about 12 logical tests when I’m done and will adding more later

Basically, a text in a cell will depend on what is in another cell

Example…if I put the statement in C1, and I put in “abc” in A1,C1 becomes “jkl”

I couldn’t get it to work without putting in all The A1’s

SO, could I write it a different way and only put in the cell reference once, since the statement is only for one cell



mike

 

[shaowu459]

Maintain the criterias and results in other columns, then a smiple VLOOKUP will do. It is easy for you to add, remove or modify criterias and results.

工作簿1
	A	B	C	D	E	F
1	abc		jkl		Keyword	Return
2					abc	jkl
3					def	jkl
4					ghi	jkl
5					mno	stu
6					pqr	stu
7
Sheet1

Cell Formulas
Range	Formula
C1	C1	=IFERROR(VLOOKUP(A1,E:F,2,),"")

 

[Fluff]

could I write it a different way and only put in the cell reference once

Yes, you can do it like
=IF(OR(A1={"abc","def","ghi"}),"jkl",IF(OR(A1={"mno","pqr"}),"stu",""))

Although I would use a lookup table.

 

[Joe4]

Yet another possible option is to create a User Defined Function (UDF) in VBA that does what you want.
I often do this with more complex functions that would be difficult to create or maintain in Excel as regular functions.

 

[still learning]

Hi All,I hope all your loved ones are safe
Thank you, Shaowu459
I never thought about a vlookup.
that would make it easier to edit the criteria.
I'm glad you showed me the other way, Fluff. Always learning, learning, learning
Joe4...I've never did a UDF. Something to learn !!!!!

mike

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[Joe4]

Regarding UDFs, here is a short description:

How to Create Custom Excel Functions - dummies

You can create your own Excel functions, from very simple to sophisticated, with its Visual Basic Editor (VBA). Here's how to get started.

www.dummies.com

and here is a more detailed description:

Creating a User Defined Function (UDF) in Excel VBA [Ultimate Guide]

In this tutorial, I will show you how to create a User Defined Function (VBA) in VBA. You can use these UDFs in worksheets or can call the function using VBA. These functions are useful when you want to do something that can not be done with inbuilt Excel functions.

trumpexcel.com

And here is an example of one I created yesterday: Alphanumeric to Numeric

 

[still learning]

Thanks Joe 4
I'll look it over

mike

 

[Joe4]

You are welcome. In this case, if it is easy enough to do with a formula, like the one's posted by Fluff and shaowu459, then it would probably be overkill to do that in this case.
But it a good "tool" to have in your toolbox for those complex ones.
One advantage to the UDF is if you ever need to update it, you only have to update it in one place (the function in VBA), as opposed to having to update every cell that uses the formula.

 

[still learning]

I agree
I like to put things in my "tool box"