Question description:
This user has given permission to use the problem statement for this
blog.
Use the HLOOKUP function in cell H11 to calculate freight cost based on the shipping class and state to which the shipment is going. Freight Cost can be referenced on the "Freight Cost" lookup table (range E5:N8). Be sure to use appropriate relative and absolute cell references.
Hint: You will need to use a function within the HLOOKUP function to determine the appropriate row.
Solved by M. B. in 40 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
19/09/2018 - 02:28
Welcome to Excelchat, I see that your question is about Formulas.
User
19/09/2018 - 02:28
yeah
Excelchat Expert
19/09/2018 - 02:28
I can help you with that problem, I'll be able to give you a solution and an explanation. Before we get started, this is a reminder that our policy is 1 problem per session with additional Q&A on that problem as time allows.
Excelchat Expert
19/09/2018 - 02:28
Let me ask you a couple of quick questions to make sure I fully understand your problem.
Excelchat Expert
19/09/2018 - 02:28
May you please share the data you have so I can help you with your concern? You can also paste a sample data on the document preview pane.
User
19/09/2018 - 02:29
ok
User
19/09/2018 - 02:30
Use the HLOOKUP function in cell H11 to calculate freight cost based on the shipping class and state to which the shipment is going. Freight Cost can be referenced on the "Freight Cost" lookup table (range E5:N8). Be sure to use appropriate relative and absolute cell references.
Hint: You will need to use a function within the HLOOKUP function to determine the appropriate row.
User
19/09/2018 - 02:30
and the question is this
User
19/09/2018 - 02:31
i cant find a function to search it
User
19/09/2018 - 02:31
is the hlookup only can find one data?
Excelchat Expert
19/09/2018 - 02:31
Based on what you've shared, you need HLOOKUP function to get the Freight Cost on cell H11 by using the Freight Cost table on range E5:N8. Do you believe that will address your problem?
User
19/09/2018 - 02:32
yes
User
19/09/2018 - 02:32
but now i m not sure about my range
Excelchat Expert
19/09/2018 - 02:32
Ok I'm going to create the formula needed to get the output you want. This should take me less than 20 minutes. If we need more than 20 minutes, you'll be able to extend the session up to 60 total minutes.
User
19/09/2018 - 02:33
thank you very much
Excelchat Expert
19/09/2018 - 02:34
I'll be working on the shared file. When our session is over you'll be able to access the solution in your session history by downloading the Excel File.
User
19/09/2018 - 02:34
ok
User
19/09/2018 - 02:40
Hi do I have to wait until the time is up?
Excelchat Expert
19/09/2018 - 02:42
Yes so we can have discussion once I'm done with the formula. But if you need to go, you can come back again later.
User
19/09/2018 - 02:42
ok i got u
Excelchat Expert
19/09/2018 - 02:45
I'm still working, I should have your solution soon.
User
19/09/2018 - 02:47
ok
User
19/09/2018 - 02:47
take ur time
Excelchat Expert
19/09/2018 - 02:51
Thank you for your patience.
Excelchat Expert
19/09/2018 - 02:52
Here's the formula you need: =HLOOKUP($B11,$E$5:$N$8,MATCH($F11,$E$5:$E$8,0),0)
Excelchat Expert
19/09/2018 - 02:53
It is a combination of HLOOKUP and MATCH function
User
19/09/2018 - 02:53
wow
User
19/09/2018 - 02:54
i didn't even know the MATCH function
User
19/09/2018 - 02:54
but thank you!
Excelchat Expert
19/09/2018 - 02:54
HLOOKUP function can only look horizontally across the table of freight cost to match the correct column corresponding to the state, and not down corresponding to the Shipping Class
Excelchat Expert
19/09/2018 - 02:54
You're welcome. :)
Excelchat Expert
19/09/2018 - 02:55
There was a hint on your question that you have to use other function within HLOOKUP "Hint: You will need to use a function within the HLOOKUP function to determine the appropriate row."
Excelchat Expert
19/09/2018 - 02:55
So with that, I believe that this is the best solution to your problem.
User
19/09/2018 - 02:55
yeah i saw that but how can i find a function within the hlookup function
Excelchat Expert
19/09/2018 - 02:56
Do you have any other questions about this problem and the solution?
User
19/09/2018 - 02:56
nope that's all
Excelchat Expert
19/09/2018 - 02:56
Thanks for coming to Excelchat. Feel free to leave any comments or feedback. Have a nice day!
Excelchat Expert
19/09/2018 - 02:57
Just a reminder, we are unable to end this session on our end until you do so please do not close this window without ending the session properly (Red X or End Session). Feel free to end this session as soon as you are ready for us to continue helping other customers. Thank you.
Excelchat Expert
19/09/2018 - 02:59
Hello are you still there?
Excelchat Expert
19/09/2018 - 03:00
Le me know if there's anything else I can assist you regarding this issue.
User
19/09/2018 - 03:01
ohoh
User
19/09/2018 - 03:01
sry
User
19/09/2018 - 03:02
i will close it my bad
Excelchat Expert
19/09/2018 - 03:02
No worries, thanks!
Excelchat Expert
19/09/2018 - 03:02
Thanks for using Got It Pro-Excel. Please give your kind feedback for our service. Have a good day! :)
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.
