|
IF & OR Functions
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! ============================================== "Lee" wrote in message ... 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? |
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 "RagDyer" wrote: 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! ============================================== "Lee" wrote in message ... 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? |
Lee wrote:
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 "RagDyer" wrote: 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! ============================================== "Lee" wrote in message ... 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? 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! ================================================== === "Lee" wrote in message ... 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 "RagDyer" wrote: 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! ============================================== "Lee" wrote in message ... 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? |
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 ! ---------------------------------------------------------------------------- ------------------- "RagDyeR" wrote in message ... 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! ================================================== === "Lee" wrote in message ... 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 "RagDyer" wrote: 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! ============================================== "Lee" wrote in message ... 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? |
Thanks alot I will try it
"gls858" wrote: Lee wrote: 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 "RagDyer" wrote: 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! ============================================== "Lee" wrote in message ... 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? 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 |
| All times are GMT +1. The time now is 02:30 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com