# Excel - COLUMN Function Problem - Expert Solution

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

I need a formula to check a short list of names and put the appropriate value for that name in. For example, "Supermarket 1" and the value "20%" and "Supermarket 2" with "30%", so each time Column A has "Supermarket A" written in a cell, the appropriate cell in Column B will autofill with the right value instead of having to consult the list and manually enter it each time. This value will then be checked by later formulae
Solved by E. J. in 37 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 09/07/2018 - 03:28
Hi there!
User 09/07/2018 - 03:29
You don't leave a lot of time to answer before you get antsy
User 09/07/2018 - 03:29
Is this a person now?
Excelchat Expert 09/07/2018 - 03:29
Apologies! Do you mean the questions?
User 09/07/2018 - 03:29
I do
Excelchat Expert 09/07/2018 - 03:29
Yes I am a person... sorry for the hassle!
Excelchat Expert 09/07/2018 - 03:29
I can relay this feedback for sure. I was thinking the same thing actually...
User 09/07/2018 - 03:29
Alright. Did you receive a copy of my issue?
Excelchat Expert 09/07/2018 - 03:30
Anyhow, I am sure I can help you. Do you have a file you are able to share so I can code in the formula for you?
Excelchat Expert 09/07/2018 - 03:30
I sure did.
User 09/07/2018 - 03:30
I'm afraid it has confidential client information on it.
User 09/07/2018 - 03:30
... Do you want me to send a blank worksheet or something?
Excelchat Expert 09/07/2018 - 03:31
If you can possibly remove the sensitive info?
Excelchat Expert 09/07/2018 - 03:31
I just would need to know where the various supermarket values are...
Excelchat Expert 09/07/2018 - 03:32
and where the formula goes, and which cells the formula will be referencing... things like that.
User 09/07/2018 - 03:32
It's not actually supermarkets, that was an example
Excelchat Expert 09/07/2018 - 03:32
We can make an example in the google sheet over there ---------> if you can't send me anything.
User 09/07/2018 - 03:33
I wasn't expecting this, I'm afraid. It would take some editing on my end. I'll set up what I can on here
Excelchat Expert 09/07/2018 - 03:33
OK fantastic
User 09/07/2018 - 03:34
Right, so column E will frequently have variable entries, either CTM, MSM or QLC, with very rare exceptions
User 09/07/2018 - 03:35
Each of these will correspond to the values in column D
User 09/07/2018 - 03:35
Again, the values are estimates. They actually involve decimal places
Excelchat Expert 09/07/2018 - 03:35
So column D is the one that needs to be automated?
Excelchat Expert 09/07/2018 - 03:36
As in pull the % from a different section for the corresponding Value Source?
User 09/07/2018 - 03:36
The % will apply to the income, and a SUMIF formula in F2 will take the Income and % of those that have a value in column B, which will indicate whether or not it's sold.
User 09/07/2018 - 03:36
Yes
User 09/07/2018 - 03:36
Essentially
User 09/07/2018 - 03:36
I was thinking...
Excelchat Expert 09/07/2018 - 03:37
Roger that
Excelchat Expert 09/07/2018 - 03:37
Can we have the source with the values to the right vs. the left?
Excelchat Expert 09/07/2018 - 03:37
Like this
User 09/07/2018 - 03:38
That would be no problem, for either the source or the cells where the formula would go. Easily done.
Excelchat Expert 09/07/2018 - 03:38
Perfect
Excelchat Expert 09/07/2018 - 03:38
I will code this and explain it to you and you can give it a go on your file.
User 09/07/2018 - 03:39
Cheers :)
Excelchat Expert 09/07/2018 - 03:39
Regarding the "Other"
Excelchat Expert 09/07/2018 - 03:40
Would Other = anything else other than CTM, MSM and QLC?
User 09/07/2018 - 03:40
Yes.
User 09/07/2018 - 03:41
Sort of a "if it hasn't been specified, this will be the standard" rather than "if it hasn't been specified, nothing happens"
Excelchat Expert 09/07/2018 - 03:41
Right on. Let me know if that works on your end -
Excelchat Expert 09/07/2018 - 03:42
I am happy to explain the logic and would love if you could apply the formula to your file and see if it works.
Excelchat Expert 09/07/2018 - 03:42
I didn't code the SUMIF function...
Excelchat Expert 09/07/2018 - 03:42
I can though if need be.
User 09/07/2018 - 03:45
Okay, I've pasted it in and haven't quite got it working
User 09/07/2018 - 03:46
=IF(H2="","", IFERROR(VLOOKUP(H2,\$S\$8:\$T\$12,2,0),50))
User 09/07/2018 - 03:46
H2 is my version of the D column
User 09/07/2018 - 03:46
S8 through T12 is my I1:J4
User 09/07/2018 - 03:46
I'm getting a return of "0"
Excelchat Expert 09/07/2018 - 03:47
Gotcha - let me see...
Excelchat Expert 09/07/2018 - 03:47
User 09/07/2018 - 03:47
Nevermind, I caught it
Excelchat Expert 09/07/2018 - 03:47
needs to be the column with the Value Source in it
User 09/07/2018 - 03:47
Yes
Excelchat Expert 09/07/2018 - 03:47
Oh very nice!
User 09/07/2018 - 03:47
So I replace H2 with K2 and it works
User 09/07/2018 - 03:47
Awesome!
Excelchat Expert 09/07/2018 - 03:47
Sweet!
Excelchat Expert 09/07/2018 - 03:48
You did it :)
User 09/07/2018 - 03:48
... What do the "\$" symbols mean?
Excelchat Expert 09/07/2018 - 03:48
Good question
Excelchat Expert 09/07/2018 - 03:48
They keep the lookup range static.
Excelchat Expert 09/07/2018 - 03:48
As in, if you did not have them, and you dragged the formula down...
Excelchat Expert 09/07/2018 - 03:48
the range would change from S8:T12 to S9:T13
Excelchat Expert 09/07/2018 - 03:48
and so on
Excelchat Expert 09/07/2018 - 03:49
Which mucks things up.
User 09/07/2018 - 03:49
Ah, so the formula in H3 will still look at S8 instead of S9... That's pretty clever!
Excelchat Expert 09/07/2018 - 03:49
100%
User 09/07/2018 - 03:49
So my understanding is this:
User 09/07/2018 - 03:50
If the cell with writing in is empty, come back empty, but if not (the IFERROR), then we look at this little table/cluster of cells and see what they're doing...
Excelchat Expert 09/07/2018 - 03:50
Very very close
User 09/07/2018 - 03:50
We tell it what value to look at (E6 and what's written there), where the range is to check is and find a matching value (?), and then a bunch of numbers...
User 09/07/2018 - 03:51
Ignore the second "is"
User 09/07/2018 - 03:51
Not sure what the other numbers do. That's where I'm stuck. And I might be stuck elsewhere and just not know it.
Excelchat Expert 09/07/2018 - 03:52
I can explain!
Excelchat Expert 09/07/2018 - 03:52
I shall break it down...
Excelchat Expert 09/07/2018 - 03:52
=IF(H2="","",
Excelchat Expert 09/07/2018 - 03:52
so that is the part that says if the cell in column H is blank, then put nothing
Excelchat Expert 09/07/2018 - 03:53
The IFERROR part is the piece that returns the 50
Excelchat Expert 09/07/2018 - 03:53
as in, if the VLOOKUP returns an error (as in, the value in H is NOT in the lookup range) it will put 50.
Excelchat Expert 09/07/2018 - 03:53
Basically making 50 a blanket for everyting that is NOT in the range
Excelchat Expert 09/07/2018 - 03:53
Excelchat Expert 09/07/2018 - 03:54
That's what that 50 is at the end of the formula
User 09/07/2018 - 03:54
Ah, sweet!
Excelchat Expert 09/07/2018 - 03:54
Lastly - this part of the VLOOKUP (2,0)
Excelchat Expert 09/07/2018 - 03:54
the end part of it...
Excelchat Expert 09/07/2018 - 03:54
So the 2 means to look 2 columns over from the leftmost column in the source range
Excelchat Expert 09/07/2018 - 03:54
Like, check out the google sheet
Excelchat Expert 09/07/2018 - 03:55
If you wanted the formula to return the RED numbers instead of the black ones...
Excelchat Expert 09/07/2018 - 03:55
you'd put a 3 instead of a 2
Excelchat Expert 09/07/2018 - 03:55
and the 0 means to lookup an exact match
Excelchat Expert 09/07/2018 - 03:56
Mind you, if you needed to get the red numbers, you would also have to update your source range to include that extra column as well.
Excelchat Expert 09/07/2018 - 03:56
Let me know if that makes sense!
User 09/07/2018 - 03:57
The 2 being a 3 to get the red numbers isn't making sense... if 0 is Column I, the start of the range and so presumably that's why it's considered 0, the start of the range and 0 steps away from said start... why is the next column "2" and not "1"?
Excelchat Expert 09/07/2018 - 03:57
I have thought of the same question :)
Excelchat Expert 09/07/2018 - 03:57
Excelchat Expert 09/07/2018 - 03:58
The leftmost column in a range is always the 1st column
User 09/07/2018 - 03:59
So it's 0 and 1 at the same time? Does 0 refer to the range but not the column? Or have I misunderstood the 0?
User 09/07/2018 - 04:01
Long story short, so long as I keep my ranges 2 columns wide when doing this VLookup part of the formula, if I keep the 2, 0 part it'll work?
Excelchat Expert 09/07/2018 - 04:02
The 0 is not referencing anything to do with the range. That element is to determine if the lookup will find the closest or exact match
Excelchat Expert 09/07/2018 - 04:02
You would want it to be 0 for sure
Excelchat Expert 09/07/2018 - 04:02
And yes totally to your question
User 09/07/2018 - 04:02
Ah, okay. That's cleared that up. :)
Excelchat Expert 09/07/2018 - 04:02
Amazing!
User 09/07/2018 - 04:03
Right, I've got it working, I know enough to bodge the job if things go a little crazy; if it goes bonkers I'll get back to you :) Thank you
Excelchat Expert 09/07/2018 - 04:03
That's such great news - apologies again for the fast questions at the beginning, and yes if you need more help please don't hesitate to reach back out with a new session and we will be happy to help!
Excelchat Expert 09/07/2018 - 04:04
It was great working with you - all the very best ~
User 09/07/2018 - 04:05
Brilliant. The fast questions is one thing, it's the impatience of the bot that got my goat :P you should hire one of those guys that program the sex bots that try to get my credit card number. Now thhey have patience... not tact, but they're patient! Haha
Excelchat Expert 09/07/2018 - 04:05
hahahahaha!! Indeed!
User 09/07/2018 - 04:06
Anyway, take care mate. Bye

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.