# Excel - COLUMN Function Problem - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

Write an Excel formula to determine the packaging and loading costs for this item based on its box size and weight. This cost should also include the box price but do not include any surcharges for fragile or high value items. Copy the formula down the column to calculate this value for each corresponding item.
Solved by K. Y. in 31 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 10/10/2018 - 02:22
Hello
Excelchat Expert 10/10/2018 - 02:22
Excelchat Expert 10/10/2018 - 02:22
Excelchat Expert 10/10/2018 - 02:22
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 10/10/2018 - 02:23
Excelchat Expert 10/10/2018 - 02:24
Are you still there?
User 10/10/2018 - 02:25
Yes my question is: Determine the Packaging and Loading costs in cell packlist!F3. Write an Excel formula to determine the packaging and loading costs for this item based on its box size and weight. This cost should also include the box price but do not include any surcharges for fragile or high value items. Copy the formula down the column to calculate this value for each corresponding item.
Excelchat Expert 10/10/2018 - 02:26
Please allow me a minute to relate it with the sample sheet you have made.
User 10/10/2018 - 02:27
Thank you
Excelchat Expert 10/10/2018 - 02:31
Ok.
Excelchat Expert 10/10/2018 - 02:32
So we need to calculate the value in F3 using the LBS (weight) according to the weight on boxes tab.
Excelchat Expert 10/10/2018 - 02:33
Please allow me a minute to do so.
Excelchat Expert 10/10/2018 - 02:34
=if(C3<100,hlookup(B3,boxes!B2:K9,7,false),hlookup(B3,boxes!B2:K9,8,false))
Excelchat Expert 10/10/2018 - 02:34
this is the formula I have used.
Excelchat Expert 10/10/2018 - 02:34
Excelchat Expert 10/10/2018 - 02:35
I have used IF formula along with HLOOKUP function.
Excelchat Expert 10/10/2018 - 02:35
Please confirm this solves your problem then I will start explaining how this formula works.
Excelchat Expert 10/10/2018 - 02:35
IF(logical_expression, value_if_true, value_if_false)
Excelchat Expert 10/10/2018 - 02:36
User 10/10/2018 - 02:38
On my main worksheet , formula works for packlist!F3 but when I copy it down, it says #N/A.
Excelchat Expert 10/10/2018 - 02:38
Ok.
Excelchat Expert 10/10/2018 - 02:39
I have amended the formula but I think your cursor was on the same cell Let me amend it again.
Excelchat Expert 10/10/2018 - 02:40
=if(C3<100,hlookup(B3,boxes!\$B\$2:\$K\$9,7,false),hlookup(B3,boxes!\$B\$2:\$K\$9,8,false))
User 10/10/2018 - 02:40
When I copied the formula for F4, it worked for the rest of the cells.
Excelchat Expert 10/10/2018 - 02:40
Is it working or not working?
User 10/10/2018 - 02:41
It's working now. Thank you!
Excelchat Expert 10/10/2018 - 02:41
Great.
Excelchat Expert 10/10/2018 - 02:41
Do you want to understand how this formula works?
User 10/10/2018 - 02:42
Excelchat Expert 10/10/2018 - 02:42
Ok
Excelchat Expert 10/10/2018 - 02:42
So I have used 2 functions here IF and HLOOKUP
Excelchat Expert 10/10/2018 - 02:42
IF(logical_expression, value_if_true, value_if_false)
Excelchat Expert 10/10/2018 - 02:43
We test something under logical expression and if the result is true then it displays value_if_true otherwise it will display value_if_false.
Excelchat Expert 10/10/2018 - 02:44
logical_expression is C3<100
Excelchat Expert 10/10/2018 - 02:44
VALUE_IF_TRUE is hlookup(B3,boxes!\$B\$2:\$K\$9,7,false)
Excelchat Expert 10/10/2018 - 02:45
value _if_false is hlookup(B3,boxes!\$B\$2:\$K\$9,8,false)
Excelchat Expert 10/10/2018 - 02:45
Now let me explain how HLOOKUP works.
Excelchat Expert 10/10/2018 - 02:47
HLOOKUP(look_up_value, table_array, row_index_num, [range_look_up])
Excelchat Expert 10/10/2018 - 02:47
Lookup value is B3
Excelchat Expert 10/10/2018 - 02:47
table_array is - boxes!\$B\$2:\$K\$9
Excelchat Expert 10/10/2018 - 02:49
Now for row_index_num, row2 becomes 1, row 3 becomes 2 and so on. This is because our table_array starts with row 2.
Excelchat Expert 10/10/2018 - 02:49
since if the weight is less than 100 we will use row index 7 otherwise we will sue row index 8.
Excelchat Expert 10/10/2018 - 02:50
Does that make sense?
Excelchat Expert 10/10/2018 - 02:50
Does this solution solve your problem?
User 10/10/2018 - 02:50
Yes it does make sense. Thank you.
Excelchat Expert 10/10/2018 - 02:50
Excelchat Expert 10/10/2018 - 02:51
Thanks for coming to Excelchat. Also, if you are satisfied with my solution, please go ahead and end the session using the button on the top right hand side of this chat window and requesting you to provide good feedback and review so that I can continue my services to others.
User 10/10/2018 - 02:53
No, I don't have anymore questions. Thank you for your help and I'll make sure to provide good feedback.
Excelchat Expert 10/10/2018 - 02:53
Thank you and have a great day ahead.

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.