Excel - IF Function Problem - Expert Solution

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

How can I automatically change different cells and the information they display if we select different option from a drop down list?
Solved by V. U. in 59 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 07/10/2018 - 08:40
Hello and thank you for choosing Got It Pro-ExcelChat! I can help you with your problem.
Excelchat Expert 07/10/2018 - 08:40
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. Let me ask you a couple of quick questions to make sure I fully understand your problem. :)
Excelchat Expert 07/10/2018 - 08:41
It seems you need help with changing data once you select an option from a drop down list. Will you be able to share the file you're working on and elaborate the requirement? Thanks!
User 07/10/2018 - 08:41
Yes. Just one moment please.
Excelchat Expert 07/10/2018 - 08:43
Sure thing, will wait for your file.
User 07/10/2018 - 08:45
[Uploaded an Excel file]
Excelchat Expert 07/10/2018 - 08:46
I can see you have a drop downlist in E6 that is used as reference of the other cells in column E. What values needs to be changed if you select an option from the drop down list?
User 07/10/2018 - 08:48
basically all the columns from AK to AW. So thats the min, mid and max compensation and the segments
Excelchat Expert 07/10/2018 - 08:50
Where can we get the data to return in these columns?
User 07/10/2018 - 08:50
You can find the info under the ranges sheet
User 07/10/2018 - 08:50
Excelchat Expert 07/10/2018 - 08:51
It seems they have different naming conventions. Is compensation to be linked to column F of ranges tab?
User 07/10/2018 - 08:51
yes Column F and H
Excelchat Expert 07/10/2018 - 08:52
Oh, so in column AK of data tab, we need to combine the details of column F and H of Ranges tab?
Excelchat Expert 07/10/2018 - 08:55
Hello? :)
User 07/10/2018 - 08:55
Hm.. The range levels AD01, AD02 have different countries and those different countries have job profiles related to them which is Table H
Excelchat Expert 07/10/2018 - 08:55
Do you mean column H?
User 07/10/2018 - 08:56
Ys Column H sorry.
Excelchat Expert 07/10/2018 - 08:56
So you mean to say, in column AK of the data tab, we need to return the value from column H of ranges tab?
User 07/10/2018 - 08:56
Excelchat Expert 07/10/2018 - 08:57
Yes? :)
User 07/10/2018 - 08:58
In column AK we need to return value from column F yes
User 07/10/2018 - 08:58
as well as AM is the same
User 07/10/2018 - 08:59
then AN values are returned from column H
Excelchat Expert 07/10/2018 - 08:59
Thank you for clarifying. How about column AL?
User 07/10/2018 - 08:59
Coulmn AL would be the same date for all
Excelchat Expert 07/10/2018 - 09:00
How about Eligibility rule?
User 07/10/2018 - 09:02
eligibilty rule is ina separate workbook
Excelchat Expert 07/10/2018 - 09:02
Oh okay noted on that. I'll leave it untouched then. How about the number of segments (column AP)? Thanks! :)
User 07/10/2018 - 09:03
number of segments column AQ would be all 3
User 07/10/2018 - 09:04
Excelchat Expert 07/10/2018 - 09:05
Noted on that. Let me work on it for about 20 minutes. Thanks!
Excelchat Expert 07/10/2018 - 09:08
For the spread (column AT), where do we need to reference this?
User 07/10/2018 - 09:08
Column AQ is from Column J, column AR from Column L, Column AS is from N and the segment 1 2 and 3 from KMN respecivelt
User 07/10/2018 - 09:09
we can leave spread
Excelchat Expert 07/10/2018 - 09:09
Oh I see. Noted on that. :)
Excelchat Expert 07/10/2018 - 09:10
Already done! Please see attached for the updated file for your review.
[Uploaded an Excel file]
User 07/10/2018 - 09:12
ok. i will check
Excelchat Expert 07/10/2018 - 09:15
Sure thing, please do. Please let me know should you have any concerns.
User 07/10/2018 - 09:15
In the AM column inle row 1 is changing
Excelchat Expert 07/10/2018 - 09:16
Sorry, I thought column AM is the same with column AK.
Excelchat Expert 07/10/2018 - 09:18
Could you elaborate further on what the error in this is?
Excelchat Expert 07/10/2018 - 09:19
Could you refer to this excel file instead. I reconfigured column AM to instead be linked to column AK.
[Uploaded an Excel file]
User 07/10/2018 - 09:19
ok sure, let me see
Excelchat Expert 07/10/2018 - 09:19
So basically, AM will eventually change based on column AK's values (just replicating what AK displays).
User 07/10/2018 - 09:20
Oh okay.
User 07/10/2018 - 09:20
Yes. This is correct.
Excelchat Expert 07/10/2018 - 09:20
Glad to be of help! Does this particularly solve your main query? :)
User 07/10/2018 - 09:21
Yes it does. What about the decription column
Excelchat Expert 07/10/2018 - 09:21
Oh yes, let me do that in a quick.
Excelchat Expert 07/10/2018 - 09:22
Please check this one out and please confirm if it is as well correct. Thanks!
[Uploaded an Excel file]
User 07/10/2018 - 09:24
Yes. Seems fine :)
Excelchat Expert 07/10/2018 - 09:24
Nice to know! Do you have any more clarifications with the provided solution? :)
User 07/10/2018 - 09:24
Now can you run me down on the steps you took
Excelchat Expert 07/10/2018 - 09:26
Oh sure. Actually, if you look at it, the formula for all uses the same functions IF, INDEX and MATCH. Mainly, the IF function is only used to check if cell E6 is blank or not. If blank, the formula just returns a blank value. However, if not, it makes use of INDEX and MATCH to return the appropriate value based on the column E values.
Excelchat Expert 07/10/2018 - 09:27
MATCH mainly functions by getting the value in column E and matching it against column C of the ranges tab.
Excelchat Expert 07/10/2018 - 09:27
After it gets a match, the function memorizes the row number of this match. INDEX, on the other hand, then uses this row number to get the value on the index range.
Excelchat Expert 07/10/2018 - 09:28
So when the MATCH says that a match was found in row 10 within the match range, the INDEX function returns the value within the index range that is in row 10.
Excelchat Expert 07/10/2018 - 09:29
So fundamentally, that's how the formula works. What varies only is the index range of the different columns.
Excelchat Expert 07/10/2018 - 09:29
Like for example, the index range of column AK is column F of ranges tab while the index range of column AN is column H of the ranges tab.
Excelchat Expert 07/10/2018 - 09:30
The match range will always be column C of the ranges tab. :)
User 07/10/2018 - 09:31
Okayy...So If we were to add additional information/columns there is no need to change the formula just the index range
User 07/10/2018 - 09:31
Excelchat Expert 07/10/2018 - 09:31
That is correct.
Excelchat Expert 07/10/2018 - 09:31
Well, you will need to change the "index range" within the formula.
Excelchat Expert 07/10/2018 - 09:31
Everything else will remain the same though.
User 07/10/2018 - 09:32
okay so i shall click on which cell to do that?
User 07/10/2018 - 09:32
or which button
Excelchat Expert 07/10/2018 - 09:32
Well, you will need to check on all cells. Let me paste in the formula for cell AN6: =IF(E6="","",INDEX(Ranges!$H$3:$H$975,MATCH(E6,Ranges!$C$3:$C$975,0)+ROW(1:1)-1))
Excelchat Expert 07/10/2018 - 09:33
In here, the index range would be Ranges!$H$3:$H$975
Excelchat Expert 07/10/2018 - 09:33
This says that the index range is H3:H975 of the ranges sheet.
Excelchat Expert 07/10/2018 - 09:34
If for example you want to create another column and have this referenced to column O of ranges sheet, you can just change Ranges!$H$3:$H$975 to Ranges!$O$3:$O$975
Excelchat Expert 07/10/2018 - 09:34
Notice that what changed only was the "H" characters.
Excelchat Expert 07/10/2018 - 09:35
So eventually, the formula that you'll be using for the column referenced to column O of ranges tab would be: =IF(E6="","",INDEX(Ranges!$O$3:$O$975,MATCH(E6,Ranges!$C$3:$C$975,0)+ROW(1:1)-1))
User 07/10/2018 - 09:36
ok so the column that will be populated would be in the first argument
Excelchat Expert 07/10/2018 - 09:36
that is correct
User 07/10/2018 - 09:36
and from where we are getting the info is the 2nd argument?
User 07/10/2018 - 09:36
And the ROW will be same as well?
Excelchat Expert 07/10/2018 - 09:36
Sorry, what do you mean second argument?
Excelchat Expert 07/10/2018 - 09:37
ROW will always start at ROW(1:1). When you drag it down, notice that it changes to ROW(2:2) and ROW(3:3) and so on.
Excelchat Expert 07/10/2018 - 09:37
That's just correct.
User 07/10/2018 - 09:38
2nd argument meaning the MATCh is where we are taking the values from
User 07/10/2018 - 09:38
Excelchat Expert 07/10/2018 - 09:38
Oh I see. That would remain as is since it will always be linked to column C.
User 07/10/2018 - 09:38
Excelchat Expert 07/10/2018 - 09:38
Nice to know! Do you have any more clarifications with the provided solution? :)
User 07/10/2018 - 09:39
That will be all for today. You've been very helpful :)
Excelchat Expert 07/10/2018 - 09:39
Nice! You can now end the session via the button on your upper right (sorry, can't do it from our end). I would also appreciate it if you can leave a great feedback after this.Thanks so much for using Got it pro and have a nice 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.

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
The Allstate Corporation
United Parcel Service
Dell Inc