All solutions COLUMNS Expert Solution – Excel COLUMN Problems

Excel - COLUMN Function Problem - Expert Solution

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

I need a range of numbers to become the number in the next column
Solved by D. F. in 47 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 14/08/2018 - 05:30
Hello
Excelchat Expert 14/08/2018 - 05:30
Welcome
User 14/08/2018 - 05:30
Thank you
Excelchat Expert 14/08/2018 - 05:31
Please show me an example of what you need on the blank spreadsheet.
User 14/08/2018 - 05:31
Well I need a formula to create a range of numbers into one number
User 14/08/2018 - 05:32
for example the weights for 145-160 is size 125
User 14/08/2018 - 05:32
so if i put in 146 i want 125 to pop up in the next row
Excelchat Expert 14/08/2018 - 05:33
I'm sorry, but that doesn't explain much yet.
User 14/08/2018 - 05:33
What other information do you need?
Excelchat Expert 14/08/2018 - 05:33
Do you have a file?
User 14/08/2018 - 05:34
well i have a table that i already have a macro for to put in alphabetical order but i need a formula to show what size each weight is
Excelchat Expert 14/08/2018 - 05:34
Actually I don't need anything else if the problem is just turning numbers from 145-160 in a cell to 125 in another cell. But I presume that is not all of the problem.
User 14/08/2018 - 05:34
and because it has a macro it wouldn't let me upload it
User 14/08/2018 - 05:35
well i have more than one range that goes to a size
Excelchat Expert 14/08/2018 - 05:36
Please show me the table, you can just copy/paste from your file to the blank sheet on the right.
Excelchat Expert 14/08/2018 - 05:36
You can also remove the macro temporarily and then upload the xlsx file.
User 14/08/2018 - 05:38
that is the ranges
[Uploaded an Excel file]
Excelchat Expert 14/08/2018 - 05:38
Thank you, now I have clear idea of what you need.
User 14/08/2018 - 05:38
that excel that got posted is not correct
User 14/08/2018 - 05:38
what i typed on the google one is correct
Excelchat Expert 14/08/2018 - 05:38
Understood.
Excelchat Expert 14/08/2018 - 05:40
Now I need to know what would you have as input.
Excelchat Expert 14/08/2018 - 05:41
Is it something like what I wrote on column F and G?
User 14/08/2018 - 05:41
yes, this is what i have minus the macro
[Uploaded an Excel file]
Excelchat Expert 14/08/2018 - 05:42
Sorry, I don't see anything other than column headers in the file.
User 14/08/2018 - 05:43
it's a table, with the weight being put into column a and i want the size going into column b
Excelchat Expert 14/08/2018 - 05:43
Based on the table you have showed me on the preview window?
User 14/08/2018 - 05:43
correct
User 14/08/2018 - 05:44
the weight ranges are in column a and the corresponding sizes are in column b
User 14/08/2018 - 05:44
on the google sheets here
Excelchat Expert 14/08/2018 - 05:44
Weight is a single number or a range as like in the google sheets?
User 14/08/2018 - 05:45
weight will be inputted as a single number. the range is what i have posted here tho.
Excelchat Expert 14/08/2018 - 05:45
Alright, I feel like I have all info I need.
Excelchat Expert 14/08/2018 - 05:45
Thank you for the help.
User 14/08/2018 - 05:46
Sorry I had a hard time relaying the information
Excelchat Expert 14/08/2018 - 05:46
Not a problem. Everything is alright.
Excelchat Expert 14/08/2018 - 05:47
Now there are two formulas of showing the size in column B. But both will require the table. You can either choose to put the table on the same sheet or another sheet.
Excelchat Expert 14/08/2018 - 05:49
One is a very long IF formula, that can get real confusing to comprehend. The other one is a very short formula but to apply this formula we need to restructure the weight-size table.
Excelchat Expert 14/08/2018 - 05:49
Do you find me hard to understand? If so please let me know.
User 14/08/2018 - 05:49
I'm following just fine
User 14/08/2018 - 05:50
What kind of restructure would it be
User 14/08/2018 - 05:50
?
Excelchat Expert 14/08/2018 - 05:50
Let me show you..
Excelchat Expert 14/08/2018 - 05:52
Are you able to see what I wrote on column D:F?
User 14/08/2018 - 05:52
Yes
Excelchat Expert 14/08/2018 - 05:54
That is what I was talking about. Thank you for completing it.
User 14/08/2018 - 05:54
Your welcome
Excelchat Expert 14/08/2018 - 05:54
Now, let's assume weight would be inputted in column K and you want size to appear in column L automatically.
Excelchat Expert 14/08/2018 - 05:55
So, I will write a formula for that. Please give me a few minutes.
User 14/08/2018 - 05:55
Okay, thank you
Excelchat Expert 14/08/2018 - 05:58
So this is the formula in column L =VLOOKUP(K2,$D$2:$F$9,3,TRUE)
Excelchat Expert 14/08/2018 - 05:58
Now input a number in K2 and you will see size automatically changes.
Excelchat Expert 14/08/2018 - 06:01
Let me know if you like it or not and if you approve, I'll explain how it works.
User 14/08/2018 - 06:01
That's perfect
Excelchat Expert 14/08/2018 - 06:02
Great.
Excelchat Expert 14/08/2018 - 06:02
So as I have mentioned earlier, we need to setup a table of weight/size first as showed in the google sheets.
Excelchat Expert 14/08/2018 - 06:03
Once it is there, we then write the formula VLOOKUP(K2,$D$2:$F$9,3,TRUE) assuming size would be inputted in cell K2.
Excelchat Expert 14/08/2018 - 06:04
Breakdown of the formula---
Excelchat Expert 14/08/2018 - 06:04
K2 is the cell where size would be inputted.
Excelchat Expert 14/08/2018 - 06:05
D2:F9 is the range where we have weight/size table.
Excelchat Expert 14/08/2018 - 06:06
We wrote $D$2:$F$9 (wrapped the range with dollar sign) because we don't want the range to change if the formula is copied down.
Excelchat Expert 14/08/2018 - 06:06
Am I making sense?
User 14/08/2018 - 06:07
So far
Excelchat Expert 14/08/2018 - 06:08
Great to hear that. Now we wrote 3 in the formula cause there are 3 columns in weight/size table and we want the size which is in 3rd column.
Excelchat Expert 14/08/2018 - 06:09
If the size were in 2nd column of weight/size table, then we would have wrote 2.
Excelchat Expert 14/08/2018 - 06:09
Makes sense?
User 14/08/2018 - 06:09
Yes
Excelchat Expert 14/08/2018 - 06:10
And lastly we wrote TRUE because our weight/column table is sorted.
Excelchat Expert 14/08/2018 - 06:11
That would be all, please let me know if you have any question.
User 14/08/2018 - 06:11
It comes up with a circular reference warning
Excelchat Expert 14/08/2018 - 06:11
may I see what have you wrote?
User 14/08/2018 - 06:12
VLOOKUP(A3,A52:C59,3,TRUE)
Excelchat Expert 14/08/2018 - 06:12
In which cell you put this formula?
User 14/08/2018 - 06:12
A3
Excelchat Expert 14/08/2018 - 06:13
That's why.
User 14/08/2018 - 06:13
Oh okay, where do i input the formula then?
Excelchat Expert 14/08/2018 - 06:13
If A3 is the weight then you write the formula in another cell other than A3.
Excelchat Expert 14/08/2018 - 06:14
Please look at the reference google sheet. K2 contains the weight, and L2 contains the formula.
User 14/08/2018 - 06:14
Ohhh
User 14/08/2018 - 06:14
Thank you very much
User 14/08/2018 - 06:14
It's all working now
User 14/08/2018 - 06:14
That's beautiful
Excelchat Expert 14/08/2018 - 06:15
I'm glad to be able to help.
Excelchat Expert 14/08/2018 - 06:15
Thank you for you patience.
Excelchat Expert 14/08/2018 - 06:17
If you have any question, you can ask me. Otherwise please close the chat. And also consider rating me 5 star after closing the chat if you think you were benefited from my service.

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.

Click here to get your free Excelchat help session

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc