Excel - How to Use a VLOOKUP Formula - Expert Solution

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.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.