# Nested If Formula - Using Text to get a number as a result

Dot Glindemann used Ask the Experts™
on
I am having an issue with a nested IF Formula. In column J, the result will either be 6 monthly, Annually, 2 yearly, 5 yearly or No data.

I have tried the folowing formula in column K as I want to use to result from column J (eg) 180, 365 etc as part of a formula to calculate the actual renewal date

=IF(J13=”6 Monthly”,180,IF(J13=”Annually”,365,IF(J13=”2 Yearly”,730,IF(J13=”5 Yearly”,1825,”n/a”))))

When I use this formula, I get the result #NAME?. I have tried to evaluate the formula but it does not show that there are any errors with my formula.

I know I can have a drop down in column K with each of the time options available and then use this cell reference in my formula but I am sure that there is a way to get the result I want without having to use a drop down option.

Any assistance would be greatly appreciated.
Thanks
Dot
Excel-Example-for-Nested-If-Functio.xlsx
Comment
Watch Question

Do more with

EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
You used wrong types of quotes, you need to use the double quotes instead...

Try this...
``````=IF(J13="6 Monthly",180,IF(J13="Annually",365,IF(J13="2 Yearly",730,IF(J13="5 Yearly",1825,"n/a"))))
``````
Excel-Example-for-Nested-If-Functio.xlsx
Finance Analyst

Commented:
I believe it relates to your use of double quotes, they are not true double quotes.

Try this:
=IF(J13="6 Monthly",180,IF(J13="Annually",365,IF(J13="2 Yearly",730,IF(J13="5 Yearly",1825,"n/a"))))

” has ASCII code 148 whereas
" has ASCII code 34
Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015
Commented:
You may also use the following formula to get the same output...

``````=IFERROR(CHOOSE(MATCH(J13,{"6 Monthly","Annually","2 Yearly","5 Yearly"},0),180,365,730,1825),"N/A")
``````
Finance Analyst

Commented:
Alternatively, you already have the Dropdown options in a list, use that list for a vlookup.

On your Data Validation sheet enter the number of days for each period in column B then use this formula on your name sheet:

=VLOOKUP(J13,'Data Validation'!\$A\$1:\$B\$4,2,FALSE)

If the lookup does not match a value the error result will be #N/A. If you specifically want just "n/a" then use:

=IFERROR(VLOOKUP(J13,'Data Validation'!\$A\$1:\$B\$4,2,FALSE),"n/a")
Finance Analyst
Commented:
Or this can be copied down the whole of column K even if J is blank:

=IF(J13="","",IFERROR(VLOOKUP(J13,'Data Validation'!\$A\$1:\$B\$4,2,FALSE),"n/a"))

Commented:
Thank You Subodh and Rob,

Both suggestions were very helpful but I do not have any experience yet using VLookUp (Rob) so for now I will work with what I know.

This has helped me very much.

I hope you both have a great weekend.

Thanks Dot
Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
You're welcome! Glad it worked as desired.
Thanks and you too!
Finance Analyst

Commented: