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
I think your H column
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
Essentially your "Others"
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
I meant to add that nuance in... good you asked.
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.
