Help with a long if then else statement in Excel using VBA

I am hoping this will be decently easy for you experts!! I have an excel formula that already exists in my spreadsheet. The problem that I am having is that I need to add quite a bit more logic to it. I have a variable named months this is tied to a spreadsheet in the workbook.. "bookmarks" is the name. Here is the formula now..

=IF(ISNA(VLOOKUP(Years,Bookmarks!C39:D43,2)),1,VLOOKUP(Years,Bookmarks!C39:D43,2))

I need to add to this formula

If month (- this is the variable name) =

0 to 11     then 0
12 to 18   then 1
19 to 30   then 1.5
31 to 42   then 2
43 to 54   then 2.5
55 to 60   then 4

can someone help me with this?? Pretty Please??
Jennifer BarmanAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

OCDanCommented:
Do you have some sample data to look at? And what you would expect the result to be given certain data?

Sorry for the questions I am just pretty unclear on what you are after here.
Glenn RayExcel VBA DeveloperCommented:
I see you're returning "1" if the VLOOKUP fails.  

Are now wanting to return one of the possible corresponding values in your list that depends on the value for the variable "months"?

If so, the formula would be simpler (and easier to edit) if you had another lookup list to use for "months" and its lookup value.

-Glenn

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jennifer BarmanAuthor Commented:
well this populates to a cell.. and is figured into a  calculation after the fact.

I am not extreamly familiar with vlookup or how the first statement was made. All I need to do is add the information below to the formula bar.. something like a nested if then??

Not really sure what you are wanting for example data. Right now it is creating a multiplier on our commissions sheet.
Become a CompTIA Certified Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Jennifer BarmanAuthor Commented:
I actually have created a list in bookmarks.. I guess I am not sure how to write this out?? I can give you the cell names? first the list also comes from bookmarks.. the cell range is P16:P21
Glenn RayExcel VBA DeveloperCommented:
Okay, if P16:P21 is the list for the month range, you need to have the appropriate multipliers adjacent to them.  Like so:

P16 = 0
P17 = 12
P18 = 19
P19 = 31
P20 = 43
P21 = 55

Then, the multipliers
Q16 = 0
Q17 = 1
Q18 = 1.5
Q19 = 2
Q20 = 2.5
Q21 = 4

Then, replace the "1" in your original formula (the "True" condition of your IF statement) like so:

=IF(ISNA(VLOOKUP(Years,Bookmarks!C39:D43,2)),VLOOKUP(months,$P$16:$Q$21,2,TRUE),VLOOKUP(Years,Bookmarks!C39:D43,2))

Of course, I'm still assuming you're replacing the error value of "1" with the multiplier based on the months value.  If that's incorrect, you'll need to further clarify what you want to do.

-Glenn
Saqib Husain, SyedEngineerCommented:
You can use this formula without using a range

=VLOOKUP(month,{0,0;12,1;19,1.5;31,2;43,2.5;55,4},2)

if you need a not found condition you can use

=if(isna(VLOOKUP(month,{0,0;12,1;19,1.5;31,2;43,2.5;55,4},2)),1,VLOOKUP(month,{0,0;12,1;19,1.5;31,2;43,2.5;55,4},2))

or in 2007 or later

=iferror(VLOOKUP(month,{0,0;12,1;19,1.5;31,2;43,2.5;55,4},2),1)
Jennifer BarmanAuthor Commented:
I tried the above query (Glenn).. following your directions.. but it now jsut say #N/A.. :(

any ideas?

ssaquhb... can you figure out how to add my existing formula with my new data.. I have to have the old data in the collection.
Glenn RayExcel VBA DeveloperCommented:
CJ,

In any blank cell, insert the following formula:

=VLOOKUP(months,Bookmarks!$P$16:$Q$21,2,TRUE)

If the range/variable "months" is not blank and is a number, AND if you populated the range of P16:Q21 as I noted above, you will get a result.  The "Bookmarks!" part here may be what is missing.

-Glenn
Saqib Husain, SyedEngineerCommented:
=IF(ISNA(VLOOKUP(Years,Bookmarks!C39:D43,2)),1,VLOOKUP(Years,Bookmarks!C39:D43,2))+if(isna(VLOOKUP(month,{0,0;12,1;19,1.5;31,2;43,2.5;55,4},2)),1,VLOOKUP(month,{0,0;12,1;19,1.5;31,2;43,2.5;55,4},2))
Rob HensonFinance AnalystCommented:
Just looking at some of the solutions provided and I notice that only Glenn's comment 37768673 has the TRUE/FALSE indicator for the lookup formula.

In theory without the indicator the lookup will never fail because it will return the closest match therefore the IF(ISNA(VLOOKUP...) will always go to the second option as the ISNA condition will not occur.

Thanks
Rob H
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.