Question description:
This user has given permission to use the problem statement for this
blog.
Question 5.
In cell B18, create a formula using nested IF functions and VLOOKUP functions to determine what to do with the returns. Use the lookup table in the Return Data worksheet. Refer to Figure 8-46 for some hints on how to create the formula.
Solved by O. E. in 33 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
08/09/2018 - 09:45
Welcome, thank you for choosing Got It Pro-Excel! I'll be glad to assist you with your concern. :)
User
08/09/2018 - 09:46
Thank you I'am having trouble with this portion of my homework.
User
08/09/2018 - 09:46
can i send you the excel file to show you?
Excelchat Expert
08/09/2018 - 09:46
Sure thing, let me help you with that. Can you provide the file you're working on for us to better assess? Thanks!
User
08/09/2018 - 09:46
this is it.
[Uploaded an Excel file]
User
08/09/2018 - 09:48
I'am on the return routing portion and it says for me to make an if nested and vlookup function for B18 and I can't solve it
Excelchat Expert
08/09/2018 - 09:49
Okay, so just to clarify, we need to return the correct inventory cart based on the selected department and resolution, and using the return data sheet as our reference. Is this correct? :)
User
08/09/2018 - 09:49
yes
Excelchat Expert
08/09/2018 - 09:50
Nice. Let me work on it for a while and update you the soonest! Just a reminder, we have a certain "one questions per session policy". For this session, we'll look at the one we just discuss. I'll let you know when I'm done. :)
User
08/09/2018 - 09:50
Okay sounds good thank you so much!
Excelchat Expert
08/09/2018 - 09:52
Btw, do you have an image of figure 8-46? It may be best if we can pattern the solution on that figure, since many alternatives exist, and the homework maybe requiring a certain one.
User
08/09/2018 - 09:53
Yes I will take a quick picture and upload it real quick for you.
User
08/09/2018 - 09:58
sorry i am having problems with my computer right now
User
08/09/2018 - 09:58
but i will try to get it as soon as i can
Excelchat Expert
08/09/2018 - 09:58
Noted on that. I can just provide to you the simplest formula so far. :)
Excelchat Expert
08/09/2018 - 09:59
Please check on the attached file for your review.
[Uploaded an Excel file]
Excelchat Expert
08/09/2018 - 09:59
I used an additional IFERROR and OR function for this particular solution.
Excelchat Expert
08/09/2018 - 10:00
Basically, the OR function checks whether cells B9, B14 OR B16 are blank. If at least one is blank, the formula just gives a blank value.
User
08/09/2018 - 10:00
https://books.google.com/books?id=S93lDAAAQBAJ&pg=PA527&lpg=PA527&dq=case+4+athey+department+store&source=bl&ots=zBi3nLroAV&sig=p9uUNaFaGBRtOstS6yEq-qIlLtw&hl=en&sa=X&ved=2ahUKEwiFgMnZsqzdAhWJxYMKHTBNDHMQ6AEwCHoECAMQAQ#v=onepage&q&f=false
User
08/09/2018 - 10:00
i have actually found it on google of the ebook and it has the figure in it sorry for the wait
Excelchat Expert
08/09/2018 - 10:02
Nice! It actually cleared out one thing, which is that the lookup value is just based on cell B14.
Excelchat Expert
08/09/2018 - 10:03
This will allow us for a much simpler formula.
Excelchat Expert
08/09/2018 - 10:06
Okay, after analyzing the figure and the file provided, I believe it's not in the Return Data that we need to reference the inventory cart, but on the Return Table worksheet.
Excelchat Expert
08/09/2018 - 10:07
This would allow us the necessary nested IF and VLOOKUP functions, which I thought was completely unnecessary for this concern.
Excelchat Expert
08/09/2018 - 10:09
Likewise, the drop down list for B14 is as well incorrectly referenced. Don't worry though, as these has all been corrected for us to proceed. :)
Excelchat Expert
08/09/2018 - 10:10
And last one thing, the book requires us to define a custom table name for the return table, which was just done. Now, we can move forward with how the book requires us to do this function.
User
08/09/2018 - 10:11
Awsome! thanks again sorry if I caused any problems in my excel spreadsheet. :(
Excelchat Expert
08/09/2018 - 10:11
It's alright. We always start somewhere and we learn along the way. Let me work on the nested IF and VLOOKUp function for a while and update you the soonest! ;)
Excelchat Expert
08/09/2018 - 10:14
Already done! Please see attached for your review. :)
[Uploaded an Excel file]
Excelchat Expert
08/09/2018 - 10:15
Just as what the book provided, the formulas include a nested IF and VLOOKUP solution to return the correct inventory cart based on department and resolution.
User
08/09/2018 - 10:17
Thank you so much for everything you are the best!!!!
Excelchat Expert
08/09/2018 - 10:18
Nice! You can now end the session via the button on your upper right (sorry, can't do it from our end). I would also appreciate it if you can leave a great feedback after this.Thanks so much for using Got it pro and have a nice day! :)
User
08/09/2018 - 10:18
You got it you Sir/Lady deserve a raise! You Rock!!
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
