| Home |
| Search |
| Today's Posts |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nested IF Function returns #NAME? Error
Thanks for the feedback :O)
-- ** John C ** "bw" wrote: John, the IF function gave me a #REF! error, but the VLOOKUP fixed the problem, THANK YOU!!! "John C" wrote: I think you also have some of the non-standard quotates. Copy and paste this formula: =IF('Comparison Chart'!H63="Select One","Select One",IF('Comparison Chart'!H63="Monthly","Monthly",IF('Comparison Chart'!H63="2.5% Discount Paid Quarterly","Quarterly",IF('Comparison Chart'!H63="5.0% Discount Paid SemiAnnually","SemiAnnually",IF('Comparison Chart'!H63="12.0% Discount Paid Annually","Annually",""))))) Or, if you prefer, you can setup a VLOOKUP within the cell. Bit longer, but I think more flexibility (and easier to decipher, and don't have to worry about too many nested IFs: =IF(ISNA(VLOOKUP('Comparison Chart'!H63,{"Select One","Select One";"Monthly","Monthly";"2.5% Discount Paid Quarterly","Quarterly";"5.0% Discount Paid SemiAnnually","SemiAnnually";"12.0% Discount Paid Annually","Annually"},2,FALSE)),"",VLOOKUP('Compar ison Chart'!H63,{"Select One","Select One";"Monthly","Monthly";"2.5% Discount Paid Quarterly","Quarterly";"5.0% Discount Paid SemiAnnually","SemiAnnually";"12.0% Discount Paid Annually","Annually"},2,FALSE)) -- ** John C ** Please remember if your question is answered, to mark it answered :). It helps everyone. "bw" wrote: Help- i can't figure out why this is not working. the error i am receiving is centered on the word Monthly, and i don't know why Excel doesn't want me to use this word. i have confirmed that the cell formating is for Text, not numbers or something else. The nesting is 5 formulas, less then the 7 max. =IF(('Comparison Chart'!H63=€śSelect One€ť),€śSelect One€ť,IF('Comparison Chart'!H63=€śMonthly€ť),€śMonthly€ť, IF('Comparison Chart'!H63=€ś2.5% Discount Paid Quarterly€ť),€śQuarterly€ť,IF('Comparison Chart'!H63=€ś5.0% Discount Paid SemiAnnually€ť),€śSemiAnnually€ť,IF('Comparison Chart'!H63=€ś12.0% Discount Paid Annually€ť),€śAnnually€ť,"") Basically if you select: Select One, Monthly, 2.5% Discount..., 5.0% Discount...,12.0% Discount..., on the sheet 'Comparison Chart' cell H63, it should return the nickname of Select One, Monthly, Quarterly, SemiAnnully, Annually, or leave it blank. but it just isnt working at all! WHY? i am at a loss i have checked my quotes, commas, and parenthesis, in addition to references and spelling. What is it? |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Nested If returns error (too many arguments) | Excel Worksheet Functions | |||
| LINEST() function returns error. | Excel Discussion (Misc queries) | |||
| Find function returns the #VALUE! error value | Excel Discussion (Misc queries) | |||
| nested if statement returns #value error | Excel Discussion (Misc queries) | |||
| AVERAGE function returns #DIV/0! error | Excel Discussion (Misc queries) | |||
![John C[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Threaded Mode
