# if statement wilh more than 12 logical formulas

#### still learning

##### Well-known Member
Hi,

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

This is what I got

Rich (BB code):
``=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

### Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

#### shaowu459

##### Well-known Member
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.

ABCDEF
1abcjklKeywordReturn
2abcjkl
3defjkl
4ghijkl
5mnostu
6pqrstu
7
Sheet1
Cell Formulas
RangeFormula
C1C1=IFERROR(VLOOKUP(A1,E:F,2,),"")

#### Fluff

##### MrExcel MVP, Moderator
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

##### Well-known Member

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

##### MrExcel MVP, Moderator
Glad we could help & thanks for the feedback.

#### Joe4

Regarding UDFs, here is a short description:
and here is a more detailed description:

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

#### still learning

##### Well-known Member
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

##### Well-known Member
I agree
I like to put things in my "tool box"

Replies
2
Views
77
Replies
3
Views
121
Replies
2
Views
136
Replies
10
Views
198
Replies
1
Views
96

1,127,639
Messages
5,626,042
Members
416,157
Latest member
anilkrsah021

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back