if statement wilh more than 12 logical formulas

still learning

Well-known Member
Joined
Jan 15, 2010
Messages
622
Office Version
  1. 365
Platform
  1. Windows
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

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

shaowu459

Well-known Member
Joined
Apr 26, 2018
Messages
534
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEF
1abcjklKeywordReturn
2abcjkl
3defjkl
4ghijkl
5mnostu
6pqrstu
7
Sheet1
Cell Formulas
RangeFormula
C1C1=IFERROR(VLOOKUP(A1,E:F,2,),"")
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,547
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,916
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jan 15, 2010
Messages
622
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Jun 12, 2014
Messages
56,547
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,916
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,916
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,536
Messages
5,636,890
Members
416,947
Latest member
asher_nk

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top