I never thought I'd see the day that an Office program would confuse me... I have an assignment in Excel that uses 'VLOOKUP' functions across multiple sheets. (Plus other stuff I think.)

I can send someone and/or upload the workbook and instructions if any of you can help me. It's pretty sad and embarrassing I can't figure this out, but I really need help.

Thanks :look:

Just put the instructions in a text file and attach it to a message.

The instructions are the jpeg. I tried many, many different things to get it to work, but I left my last attempt in there, so you can see what I tried.

Thanks :)

727896
727898

Alright so what exactly are you looking for help with?

Alright so what exactly are you looking for help with?
To start with, step 2. The lookup functions.

To start with, step 2. The lookup functions.

Based on the formula you had plugged into cell C7, the formula for the VLOOKUP is wrong. The parameters for the formula (value to look up, table of cells to dig through, which column within table to get information)

You had it with the value to look up fine. You had the range, which should have been all rows and columns of functions. So in other words, A8 - D119. The column within that table would be what is shown in C7, so it asked for the Type, so you would use the first column. In other words, the value for that third parameter is 1.

C8 and C9 are the exact same formula but the third parameter changes.

Based on the formula you had plugged into cell C7, the formula for the VLOOKUP is wrong. The parameters for the formula (value to look up, table of cells to dig through, which column within table to get information)

You had it with the value to look up fine. You had the range, which should have been all rows and columns of functions. So in other words, A8 - D119. The column within that table would be what is shown in C7, so it asked for the Type, so you would use the first column. In other words, the value for that third parameter is 1.

C8 and C9 are the exact same formula but the third parameter changes.
Hmmm. I'm still a little confused.
I tried: =VLOOKUP(B5,'Product List'!A8:A119,1) in C7. It displays 1050. It needs to display the type...

I think your second point is where my problem is, but I don't understand what you mean still.

Hmmm. I'm still a little confused.
I tried: =VLOOKUP(B5,'Product List'!A8:A119,1) in C7. It displays 1050. It needs to display the type...

I think your second point is where my problem is, but I don't understand what you mean still.

As I said, you should get the whole range of data, so it should read

VLOOKUP(B5,'Product List'!$A$8:$D$119,1)

As I said, you should get the whole range of data, so it should read

VLOOKUP(B5,'Product List'!$A$8:$D$119,1)
Still says 1050..

Still says 1050..

VLOOKUP(B5,'Product List'!$A$8:$D$119,2)

My apologies, it should be a 2. Look at the product list for yourself. There are 4 columns for ID, Type, Model, and Price. The 2nd column is the Type, the 3rd is the Model, and the 4th is the Price.

That value for the third parameter is dependent on which column you want to grab data out of in relation to the lookup value which is B5, or 1050.

VLOOKUP(B5,'Product List'!$A$8:$D$119,2)

My apologies, it should be a 2. Look at the product list for yourself. There are 4 columns for ID, Type, Model, and Price. The 2nd column is the Type, the 3rd is the Model, and the 4th is the Price.

That value for the third parameter is dependent on which column you want to grab data out of in relation to the lookup value which is B5, or 1050.
Perfecto! Thanks!

So for the next one, it should be something like: =VLOOKUP(B5,'Product List'!$C$8:$C$119,3). I know B5 is wrong, though. What should it be?

(I know I'm coming off as ignorant, but I promise I'm not. I just can't wrap my head around this for some reason. (Maybe because it's haunting me of my C++ days...))

Perfecto! Thanks!

So for the next one, it should be something like: =VLOOKUP(B5,'Product List'!$C$8:$C$119,3). I know B5 is wrong, though. What should it be?

(I know I'm coming off as ignorant, but I promise I'm not. I just can't wrap my head around this for some reason. (Maybe because it's haunting me of my C++ days...))

No, same range of $A$8:$D$119, just the third parameter needs to be changed.

No, same range of $A$8:$D$119, just the third parameter needs to be changed.
Awesome. Makes perfect sense. Thank you!!

For #3, I found this: http://www.brighthub.com/computing/windows-platform/articles/25455.aspx
Is that the best/easiest method?

Nevermind. That worked fine. That was a stupid question...

No, same range of $A$8:$D$119, just the third parameter needs to be changed.
Okay...now I'm on #5. How do I do this using D40:E42?

Anyone? :(

Okay...now I'm on #5. How do I do this using D40:E42?
Part of the problem is they aren't specifying on which worksheet to enter the shipping costs. I would have presumed it to be the Product List, *however* D40:D42 contain prices for the items -- that doesn't make any sense. I created the shipping charges, but in a new worksheet. It also doesn't state what to do if the cell contains a value that isn't Standard, Express or Overnight...

Step #6 is also a bit lame, as it doesn't say how to account for a 0 quantity. The way it's worded, you would have a shipping charge...


Anyway, here's the completed file... not 100% certain it's right, but should at least give you some ideas...

Part of the problem is they aren't specifying on which worksheet to enter the shipping costs. I would have presumed it to be the Product List, *however* D40:D42 contain prices for the items -- that doesn't make any sense. I created the shipping charges, but in a new worksheet. It also doesn't state what to do if the cell contains a value that isn't Standard, Express or Overnight...

Step #6 is also a bit lame, as it doesn't say how to account for a 0 quantity. The way it's worded, you would have a shipping charge...

Anyway, here's the completed file... not 100% certain it's right, but should at least give you some ideas...
First off, thanks! I'm looking at it right now. I agree that the instructions are poorly written.

For C7, C8, and C9, why did you do what you did? Shouldn't it just be:
=IFERROR(VLOOKUP(B5,'Product List'!$A$8:$D$119,2), "Product ID Not Found")

I think the shipping costs (#5) goes on the "Purchase Order" sheet. Considering that, how would your formulas change?
=IF(ISBLANK(B15),"",IF(B15="Standard",Shipping!A2,IF(B15="Express",Shipping!B2,IF(B15="Overnight",Shipping!C2,"Unknown"))))

728090

First off, thanks! I'm looking at it right now. I agree that the instructions are poorly written.

For C7, C8, and C9, why did you do what you did? Shouldn't it just be:
=IFERROR(VLOOKUP(B5,'Product List'!$A$8:$D$119,2), "Product ID Not Found")

I think the shipping costs (#5) goes on the "Purchase Order" sheet. Considering that, how would your formulas change?
=IF(ISBLANK(B15),"",IF(B15="Standard",Shipping!A2,IF(B15="Express",Shipping!B2,IF(B15="Overnight",Shipping!C2,"Unknown"))))

728090

For Step 5, here you go. Nested ifs are not that bad to do, just a pain in the ass to keep up with all of the parenthesese.

=(IF(B15="Standard",E40,IF(B15="Express",E41,IF(B15="Overnight",E42,"Invalid Shipping method"))))

First off, thanks! I'm looking at it right now. I agree that the instructions are poorly written.

For C7, C8, and C9, why did you do what you did? Shouldn't it just be:
=IFERROR(VLOOKUP(B5,'Product List'!$A$8:$D$119,2), "Product ID Not Found")Never knew there was a IFERROR function... definitely a lot cleaner than the IF(ISERROR process... I'm assuming/hoping this is something new introduced to Excel 2007, as I could kick myself for not knowing about it if it were in 2003...

Beyond that, two things:
1) The ",2" would only take what was in the second column (column B). This works for C7, but not for C8/C9. You could do a ",3" and a ",4", but what I did is a bit fancier and allows for simple copy/pasting down the rows. Absolute overkill, yes, but still..
2) Per your stated formula, you aren't including the optional VLOOKUP parameter to tell it what type of match to conduct. It's optional, but if you want a "EXACT" match, append a "FALSE" to the end of the function...


I think the shipping costs (#5) goes on the "Purchase Order" sheet. Considering that, how would your formulas change?
=IF(ISBLANK(B15),"",IF(B15="Standard",Shipping!A2,IF(B15="Express",Shipping!B2,IF(B15="Overnight",Shipping!C2,"Unknown"))))

728090
Too bad they don't have a "IFBLANK" function... just checked (Excel 2007) and none... but that IFERROR is definitely nice nonetheless. Back to your question -- you'd change from say ...IF(B15="Standard",Shipping!A2.... to ...IF(B15="Standard",E40... With an IF function, the first part is the condition to test... In this case, does the value in cell B15 equal "Standard"? The second part of the IF condition is what to put into the cell if the condition is TRUE. In my workbook, if the condition was TRUE, it would put the value in cell A2 in the Shipping worksheet into the cell. Or, with the new location of E40. If the condition is FALSE, it would then put whatever that value was. With the nested IF's, if the value is FALSE, you move onto a new condition to test...


For Step 5, here you go. Nested ifs are not that bad to do, just a pain in the ass to keep up with all of the parenthesese.At least Excel highlights the paired parentheses.