Excel - IF Function Problem - Expert Solution

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

I'm looking to offset from =today(), for example: If cell A13=today(), display cell A20. If cell B13=today(), display cell B20. If cell C13=today(), display cell C20. I want to do this for 365 rows. Is there a simpler way of doing this than a huge nested IF statement?
Solved by K. H. in 34 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 13/09/2018 - 10:29
Hello! How are you?
User 13/09/2018 - 10:29
Hi DT. I'm really well thanks. How are you?
Excelchat Expert 13/09/2018 - 10:29
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 13/09/2018 - 10:30
That's good to hear. I am great. Thanks for asking. :)
User 13/09/2018 - 10:30
Excelchat Expert 13/09/2018 - 10:30
Let me ask you a couple of quick questions to make sure I fully understand your problem.
Excelchat Expert 13/09/2018 - 10:31
Firstly, are you able to share a sample data on the document preview to the right, so I could better visualize your worksheet?
User 13/09/2018 - 10:33
I'm looking to have a 'home page', where I can easily see how much interest I have made today. So, if today was 25th August, I'd like the cell to show E9. If 26th, E10, etc.
User 13/09/2018 - 10:33
I started a nested IF statement
Excelchat Expert 13/09/2018 - 10:34
From your problem statement, you wanted to be able to utilize the OFFSET function on this worksheet.
User 13/09/2018 - 10:34
Ok, that sounds right
Excelchat Expert 13/09/2018 - 10:35
Are you also open to use of other formula as long as it is not a nested IF statement?
User 13/09/2018 - 10:35
Not really, no. I started to use a Nested IF statement generator, but realised there's got to be an easier way
Excelchat Expert 13/09/2018 - 10:36
You are right. Let me show you 2 sets of formula which can do the job for you. :)
User 13/09/2018 - 10:36
Thank you :D
Excelchat Expert 13/09/2018 - 10:41
Hello! Are you able to see the document preview?
User 13/09/2018 - 10:41
I can see it, yes.
Excelchat Expert 13/09/2018 - 10:41
So the formula are in cells H4 and H5, both returning the same results
User 13/09/2018 - 10:42
Excelchat Expert 13/09/2018 - 10:42
Based on the date specified in H3
Excelchat Expert 13/09/2018 - 10:44
First let me tell you how the INDEX and MATCH combo works.
Excelchat Expert 13/09/2018 - 10:44
The syntax for INDEX is INDEX(reference, [row], [column])
User 13/09/2018 - 10:44
Oh no I typed in the spreadsheet instead of here!
User 13/09/2018 - 10:44
Ok sorted
Excelchat Expert 13/09/2018 - 10:45
Excelchat Expert 13/09/2018 - 10:48
So for the index formula we can say that it returns a value of a range based on the position indicated. Our reference range is the whole of column E (or E:E). The position is determined by our MATCH formula.
Excelchat Expert 13/09/2018 - 10:49
In this example, in our formula ...MATCH(H3,A:A,0)... This will find the position of H3 (or Aug 22) in column A.
Excelchat Expert 13/09/2018 - 10:49
So MATCH(H3,A:A,0) = 6
Excelchat Expert 13/09/2018 - 10:50
And then this feeds to your INDEX formula INDEX(E:E,6)
Excelchat Expert 13/09/2018 - 10:50
It will now return the 6th row in your column E = 0.23973
Excelchat Expert 13/09/2018 - 10:52
OFFSET works similarly such that you have to first identify an initial reference point and tell excel to move by N rows and N columns after that point.
Excelchat Expert 13/09/2018 - 10:53
so in our formula =OFFSET($E$2,MATCH(H3,$A$2:$A$13,0)-1,0), where MATCH(H3,$A$2:$A$13,0)-1 = 5-1 = 4
Excelchat Expert 13/09/2018 - 10:54
We get > OFFSET($E$2,4,0)... So you could see this tells excel to move 4 rows down and 0 columns to the right. That's basically cell E6 = 0.23973
Excelchat Expert 13/09/2018 - 10:55
Clear so far?
User 13/09/2018 - 10:55
Excelchat Expert 13/09/2018 - 10:56
Cool. Could you please confirm if this solution solve your problem?
Excelchat Expert 13/09/2018 - 10:58
If you don't have any more clarifications, you may end the session any time now by clicking the END SESSION button on the upper right corner of the workspace (that's beside the countdown timer). :)
Excelchat Expert 13/09/2018 - 10:58
You may also keep a copy of this worksheet by clicking File>Download as>Microsoft Excel
User 13/09/2018 - 10:59
This does solve my problem. Thank you so much!
User 13/09/2018 - 10:59
Is this service available 24 hours a day?
Excelchat Expert 13/09/2018 - 10:59
Thanks for coming to Excelchat. Feel free to leave any comments or feedback. Have a nice day!
Excelchat Expert 13/09/2018 - 10:59
Yes. We have a pool of experts on standby to help 24 hours a day :)
User 13/09/2018 - 11:00
That's amazing. Thank you so much. Sometimes there's an issue I just can't work out so I'm so glad you're here.
User 13/09/2018 - 11:00
What is the charge noramlly?
Excelchat Expert 13/09/2018 - 11:01
Happy to hear that.
Excelchat Expert 13/09/2018 - 11:01
You may check this link for the rates
Excelchat Expert 13/09/2018 - 11:01
User 13/09/2018 - 11:01
Is there a student rate?
Excelchat Expert 13/09/2018 - 11:02
I am not so sure about that. You may e-mail sheets.support@got-it.ai for further inquiries on the rates. :)
User 13/09/2018 - 11:02
Thank you so much.
User 13/09/2018 - 11:02
Speak soon, DT.
Excelchat Expert 13/09/2018 - 11:02
You're welcome. :)
Excelchat Expert 13/09/2018 - 11:03
You may end the session any time now by clicking the END SESSION button on the upper right corner of the workspace (that's beside the countdown timer). :)

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.