Can I nest an IF in a VLOOKUP?

Hi,

I would like to know if I can nest an IF formula in a VLOOKUP. I have had two very nice people give me solutions BUT the solutions are not nesting an IF in my VLOOKUP. My VLOOKUP worked but I can not figure out how to make the IF work with it. I keep getting a VALUE error. Column I has "Date Shipped", I would like to use the 03/01/2013 format here. Column J has "Additional Shipping Costs", this is where I want to use my VLOOKUP to look on sheet 2 at a table named DOW(Day of Week). I got this far but what I can not make work is the IF it is Thurs-Sun than add Cell F14 (Cost of extra shipping for Thurs-Sun) + F15+F16. IF Mon-Wed than only add cells F15 +F16. I would greatly appreciate if someone could tell me if this is even possible or to look at my attempts on the workbook and see what I am doing wrong with my IF part.
Thanks

Original Post below-----


Hi,
I am learning excel and have a project I am working on that I need help with. I have "Date Shipped", in Column I. In Column J, I have Additional Shipping Costs. I have a table on Sheet 2 that is named DOW (Days of Week). 1=Sunday, 2=Monday, etc. I have an additional chart on Sheet 1 that explains the potential additional costs for shipping, i.e. blue ice =$5, box =$5, shipping Thursday through Sunday is an additional $10. In cell J2 I have a formula, (that worked to my amazement) it is =VLOOKUP(WEEKDAY(I2),DOW,2,FALSE) I wanted it to look at the date in I2, reference my DOW table, column 2 which is the day of the week, and give me the day of the week. It worked!!! Now the problem. I am wondering how or if I can nest another function in there that if the day of the week is Thurs, Fri, Sat, or Sun, it will reference cell F14 which is the additional fee $10 for shipping on those days, plus cells G14 and H14 for $5 for other shipping costs. If it is not Thurs-Sun then I would like it to just include shipping for G14 and H14. Is this possible? When I try the if function the info I put in seems to be fine until I hit ok then it wants I get an error or it wants to correct it and if I let it and I get an error anyway. I hope this is not too confusing. I am not sure if my attachment which has a screen print of my data made it but if it did maybe that will help to visualize. You cannot see the DOW table on sheet 2 but that should be ok. Can anyone help? I would greatly appreciate it.

Welcome to the forum Anjin.

if you could post a real "workbook" other's surely will help.
Sometimes we don't want to duplicate/retype your data.

thanks.

Hi Anjin,

This forum also allows you to attach a workbook so we can see what you're actually dealing with rather relying on a photo.

You can attach it to any post.

The DOW table isn't required.

Have a look at the attached.


Anjin 2012-08-05.xlsx

You other question is called solved.

You i get lost of all my text.

Here my example.

Yes this is possible, see my example (with the formula below).

+H2+VLookup(VLookup(WEEKDAY($I2),DOW,2,ONWAAR),DOW[[Column2]:[price]],2,0)

1) VLookup(WEEKDAY($I2),DOW,2,ONWAAR) will give as result the weekday.

This result I used for the second Vlookup.

2) VLookup(sunday,DOW[[Column2]:[price]],2,0)

This result gives the price in the table.

I hope I explained it well enough.

Indeath it's not an nested "IF"function.

But, it realy is an nested function.

The WEEKDAY function gives you options on how to number the days of the week (see Excel help for an explanation). The second option (the "2" in the function) gives you this:

1 Mon
2 Tue
3 Wed
4 Thu
5 Fri
6 Sat
7 Sun

So in my formula:

WEEKDAY(I13,2)

will give a 3 or less if the day is Mon - Wed, and 4 or greater if it is Thu - Sun.

In the IF function

IF(WEEKDAY(I13,2)>3,1,0)

will give a "1" if the day of the week is Thu - Sun and a "0" if it isn't. I then multiply that by F14 to add the shipping charge.

The DOW table and the VLOOKUP function is not required.

Yes this is possible (Nest an IF with a VLOOK)

=If(VLookup(WEEKDAy(I14),Sheet2!$A$2:$B$8,2,0)="Monday",15,12)

And now you get a second question

Yes this is possible (Nest an IF in a VLOOK)

=VLookup(If(J16=2;2;4),Sheet2!$A$2:$B$8,2,0)

Anjin,

To answer your question "Can I nest an IF in a VLOOKUP?" the answer is "Yes". oeldere has already provided two examples of how the syntax would look.

The problem you seem to be experiencing is due to you trying to "force" that approach into an example where it simply does not fit. It must make sense to do it before you can or should do it.

Hope this helps.

@anjin

Don't feel foolish, we all had to learn by trying.

I admire you perserverance.

I agree with CheshireCat

The problem you seem to be experiencing is due to you trying to "force" that approach into an example where it simply does not fit. It must make sense to do it before you can or should do it.

For your problem are better alternatives.

Just use them and learn of those options.

Glad I could help. Thanks for the reply.

If the question is solved, will you mark your question as solved?

You can do this above the first question on the middle of the page (in small letters), the most left option.

If you have (other) questions, just ask.