IF & OR Functions

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Can you use a OR function in an IF function.

I'm trying to check a cell on sheet 1 for certain text such as
"HS-C"
"HS-U"
"HS-D"
"HS-R"
"HS-L"
"HS"
"ST-C"
"ST"

if the cell has "HS-L" or "HS-U" or"HS-D" or"HS-R" the IF function would
return "U-HS-MCS, I could probally nest the others in the False setion of the
IF function. I just need to know how to group them together, without having a
long nested IF function,

By the way is there a limit to have many nested IF functions are allowed?
 
What's supposed to happen when there is no match?

Anyway, try this:

=IF(OR(A1={"HS-U","HS-D","HS-R","HS-L"}),"U-HS-MCS","No Match")
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================
 
The part you wrote worked great. This is the whole IF function

=IF(OR('A-V Input'!A5={"HS-U","HS-D","HS-R","HS-L"}),"U-MHU-MCS",IF('A-V
Input'!A5="H","U-MHU",IF('A-V Input'!A5="ST-C","U-MCS-W",IF('A-V
Input'!A5="ST","U-MCS",IF('A-V Input'!A5="HS-C","U-MHU-MCS","-")))))

It works the way I wanted. If you can shorten it up feel free I am open to
suggestions

Thanks alot
 
Lee said:
The part you wrote worked great. This is the whole IF function

=IF(OR('A-V Input'!A5={"HS-U","HS-D","HS-R","HS-L"}),"U-MHU-MCS",IF('A-V
Input'!A5="H","U-MHU",IF('A-V Input'!A5="ST-C","U-MCS-W",IF('A-V
Input'!A5="ST","U-MCS",IF('A-V Input'!A5="HS-C","U-MHU-MCS","-")))))

It works the way I wanted. If you can shorten it up feel free I am open to
suggestions

Thanks alot



:
Click to expand...

Limit to nested if statements. Took this from Help file:

Up to seven IF functions can be nested as value_if_true and value_if_false
arguments to construct more elaborate tests. (See Example 3 for a sample of
nested IF functions.) If you want to test more than seven conditions,
consider using the LOOKUP, VLOOKUP, or HLOOKUP function. (See Example 4 for
a sample of the LOOKUP function.)

gls858
 
Right off the bat, unless you have a typo, you can eliminate the *last*
argument and add it to the *first* "OR" argument.

The way to really shorten the formula is to create a data list, and
construct a formula to reference the list.
You can place this list in an out-of-the-way location of your sheet, say Y1
to Z8.
Y Z

H U-MHU
HS-C U-MHU-MCS
HS-D U-MHU-MCS
HS-L U-MHU-MCS
HS-R U-MHU-MCS
HS-U U-MHU-MCS
ST U-MCS
ST-C U-MCS-W


Then your formula could look something like this:

=LOOKUP('A-V Input'!A5,Y1:Z8)

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================


The part you wrote worked great. This is the whole IF function

=IF(OR('A-V Input'!A5={"HS-U","HS-D","HS-R","HS-L"}),"U-MHU-MCS",IF('A-V
Input'!A5="H","U-MHU",IF('A-V Input'!A5="ST-C","U-MCS-W",IF('A-V
Input'!A5="ST","U-MCS",IF('A-V Input'!A5="HS-C","U-MHU-MCS","-")))))

It works the way I wanted. If you can shorten it up feel free I am open to
suggestions

Thanks alot
 
I really screwed up the format with the paste.
Hope this looks better.

Y Z

1 H U-MHU
2 HS-C U-MHU-MCS
3 HS-D U-MHU-MCS
4 HS-L U-MHU-MCS
5 HS-R U-MHU-MCS
6 HS-U U-MHU-MCS
7 ST U-MCS
8 ST-C U-MCS-W

The *main* point here is that the list should be *sorted*.

So, follow the above.
--

Regards,

RD
----------------------------------------------------------------------------
-------------------
Please keep all correspondence within the Group, so all may benefit !
----------------------------------------------------------------------------
-------------------

Right off the bat, unless you have a typo, you can eliminate the *last*
argument and add it to the *first* "OR" argument.

The way to really shorten the formula is to create a data list, and
construct a formula to reference the list.
You can place this list in an out-of-the-way location of your sheet, say Y1
to Z8.
Y Z

H U-MHU
HS-C U-MHU-MCS
HS-D U-MHU-MCS
HS-L U-MHU-MCS
HS-R U-MHU-MCS
HS-U U-MHU-MCS
ST U-MCS
ST-C U-MCS-W


Then your formula could look something like this:

=LOOKUP('A-V Input'!A5,Y1:Z8)

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================


The part you wrote worked great. This is the whole IF function

=IF(OR('A-V Input'!A5={"HS-U","HS-D","HS-R","HS-L"}),"U-MHU-MCS",IF('A-V
Input'!A5="H","U-MHU",IF('A-V Input'!A5="ST-C","U-MCS-W",IF('A-V
Input'!A5="ST","U-MCS",IF('A-V Input'!A5="HS-C","U-MHU-MCS","-")))))

It works the way I wanted. If you can shorten it up feel free I am open to
suggestions

Thanks alot
 
Thanks alot I will try it

gls858 said:
Limit to nested if statements. Took this from Help file:

Up to seven IF functions can be nested as value_if_true and value_if_false
arguments to construct more elaborate tests. (See Example 3 for a sample of
nested IF functions.) If you want to test more than seven conditions,
consider using the LOOKUP, VLOOKUP, or HLOOKUP function. (See Example 4 for
a sample of the LOOKUP function.)

gls858
Click to expand...
 
Back
Top