Date match

Hello everyone,

I am trying to find a formula that will populate a cell based on a date range. In the example below column F needs to add the relevant company year based on if the date (in column E) falls in the company year grid. Example:
Screen Shot 2017-12-23 at 17.44.40.jpg

Below, I have manually added in the company years into column F as I cannot work out the formula that will do this for me automatically:
Screen Shot 2017-12-23 at 17.44.31.jpg


And just to further clarify. In the example below 8 February 2019 is between 1 November 2018 - 31 October 2019 and therefore falls into Year 3. Year 3 has therefore been added into column F:
Screen Shot 2017-12-23 at 17.44.10.jpg



I hope this makes sense.

Thank you in advance for any help,

Regards,

Margate

Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).

1. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential information is removed first!!

4. Try to avoid using merged cells. They cause lots of problems!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Looking at your picture, it looks like a basic lookup function with the binary/approximate match option selected. Unfortunately, it seems that almost all examples across the internet are for the linear search/exact match option, so this possibility often gets underrepresented in searching the internet.

=MATCH(E2,$B$2:$B$6,1) would return 1 (meaning first row, not necessarily first year, but, since your example shows row#=year#, this is an insignificant distinction). Note the mix of relative and absolute references so that this can easily be copied down.

If it helps, I recommend reviewing the help file https://support.office.com/en-us/art...9-533f4a37673a
Also, I have an example here using VLOOKUP() of how this binary search option works: https://www.excelforum.com/tips-and-...p-example.html

I have given up on that way as it is too complicated!

I apologize in advance for being combative, but your solution to the previously "complicated" solution is to make it even more complicated?

Before going down this new rabbit hole you are digging, can I ask what about my suggested function did not work? As simple as that function should have been to enter and copy down, I am having trouble understanding why it was "too complicated."

Why are you guys making this so complicated?

Extract the year, subtract 2015 gives your number.

Eg 2016-2015 =1

Formula for I2

Formula:

=YEAR(H2)-2015

Going back to post #1

This formula would have worked for F2:-

Formula:

=YEAR(E2)-2016+IF(MONTH(E2)>10,1,0)

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

f2=LOOKUP(E2,$B$2:$B$6,$A$2:$A$6)

=LOOKUP(E2,$B$2:$B$6,$A$2:$A$6)

Try this and copy towards down
if the result is in date format, change the cell format as general/number

what is your expected result for H10

then try
I10=

=INDEX($B$11:$F$22,MATCH(TEXT($H10,"mmmm"),$A$11:$A$22,0),MATCH(YEAR($H10),$B$10:$F$10,0))

AND COPY TOWARDS DOWN