Combining an IF fx with a VLOOKUP fx

[misspeabodi]

Greetings all!

I am in an excel class and this is the book I am using: 978-1285169330.

This question is regarding a case problem in tutorial 3, case 3, which I have provided a screen shot of.

Here is the task:

#9 in attached screen shot: revise your formulas in cells c21, e21, and g21, nesting them within an IF formula. For each cell, test whether the value of B21 is not equal to "" (a blank). If it is not, return the value of the VLOOKUP fx in cells c21 and e21, and the calculated value in cell g21. Otherwise, those cells should return a blank ("") value.

So I got as far as #8; I have attached my excel sheet where it is showing my value errors. I realize that the IF formula is supposed to show blanks (instead of #N/A) where there is no data to be calculated, but I could not figure out how to combine VLOOKUP and the IF fx.

I would appreciate insight Thank you so much!

LizHomes of Dreams.xlsxCapture.JPG

[MarvinP]

Hi Liz,

The whole idea here is - what if you type an Item ID that isn't valid. It isn't in your lookup table. It would return an error. How do you then blank out that line so the total still makes sense. See the attached.

The newer versions of Excel have a function called IFError() but the older ones needed to use If(Iserror())
See http://www.professionalexcel.com/201...ith-ifiserror/
Get an A on this exam by showing the teach you can use both.

[misspeabodi]

Ok, but I have one more question, I see the formula you put in, but now for the blank cells I have a new error message with the green little triangles in the cells saying: Formula refers to empty cells. Am I still missing something??? This step really has me perplexed with the whole nesting a formula within a formula. I looked at the book on the 'if' fx to see an example, to see what my values should be besides the "", but I can't figure it out for the life of me.


Thanks again for your help! Liz


Capture.JPG

[solotadb]

I need help how to combine an IF fx with a Vlookup fx.

thanks

[FDibbins]

solotadb welcome to the forum

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

Also, you are going to have to give more detail on your question