Array formula (think index, match)

[PGD15]

Hi,

Got an issue which is complicated so I hope this makes sense

I want to write a formula in a spreadsheet saying if the value in column a (tab 1) is equal to that from the range in column a (tab 2) then check column S for the most recent value (date is in column B (tab 2)) and return this value.


I hope that makes sense

so like july (month tab) = july (day tab), if 30th july hasnt got information (<>) then ignore it and go back each day in July until their is information and use that value.

 

[PGD15]

If we don't get it right this time, then perhaps you had better show a bit of sample data - my signature block below has a link for suggestions
- say rows 3-10 and columns A, B and AO from 'Daily breakdown'
- say rows 1-8 and columns A and AM from the summary sheet
(hide the other columns in both sheets for your screen shot to keep it small)

I am assuming that you have actual dates (eg 12 June 2018 - in any format) in A1 of the summary and column B of 'Daily breakdown'. Is that assumption correct?

See if this modification keeps it to the relevant month.

=INDEX('Daily breakdown'!AO$3:AO$10000,MATCH(AGGREGATE(14,6,'Daily breakdown'!B$3:B$10000/
(('Daily breakdown'!B$3:B$10000<=A1)*('Daily breakdown'!AO$3:AO$10000<>"")*(TEXT('Daily breakdown'!$B$3:$B$10000,"mmyyyy")=TEXT(A1,"mmyyyy"))),1),'Daily breakdown'!B$3:B$10000,0))

Sorry forgot to reply,

i presume the bit in red i was meant to swap the MM YYYY for the months and year i want? but yeah bingo this works, just hope by month end this wont pick up July's data if it doesn't you are a life safer!

even if it doesn't work will only take me a min to put an = to the month end value in the other field anyway whilst showing the current MTD value so only a a few seconds worth of work now :')

thank you very much, sorry for my poor instructions.

 

[PGD15]

One last question what does the 14,6 in the aggregate formula mean/point too? just so if i ever need to use this again I can adapt it without bugging you!

 

[Peter_SSs]

i presume the bit in red i was meant to swap the MM YYYY for the months and year i want?

You weren't supposed to 'swap' anything in the formula. The red was just to show you what I had changed from the previous formula. Perhaps I have still misunderstood a bit but what I thought was
- You have an actual date (today) in A1 of the Summary sheet. eg 12 June 2018
- Because of that, you only want to check data in 'Daily breakdown' where the row has a June 2018 date in column B.

If that is the case then you should be able to use the formula exactly as I posted it. When A1 of the summary sheet changes to a July 2018 date then the formula will automatically only look at July 2018 rows on the other sheet.
If not, and you have it working some other way, that's fine by me too.

One last question what does the 14,6 in the aggregate formula mean/point too?

- The 14 gets the AGGREGATE function to look for a MAXIMUM value. We are looking for the 'maximum' date, less than or equal to today, (& subject to other conditions) where something happens.
- The 6 tells the function to ignore errors. We use errors to our advantage by causing rows we don't want (eg blank in column AO) to result in an error, thus eliminating that row.
Look up the Help on the AGGREGATE function to learn more.

 

[PGD15]

You weren't supposed to 'swap' anything in the formula. The red was just to show you what I had changed from the previous formula. Perhaps I have still misunderstood a bit but what I thought was
- You have an actual date (today) in A1 of the Summary sheet. eg 12 June 2018
- Because of that, you only want to check data in 'Daily breakdown' where the row has a June 2018 date in column B.

If that is the case then you should be able to use the formula exactly as I posted it. When A1 of the summary sheet changes to a July 2018 date then the formula will automatically only look at July 2018 rows on the other sheet.
If not, and you have it working some other way, that's fine by me too.



- The 14 gets the AGGREGATE function to look for a MAXIMUM value. We are looking for the 'maximum' date, less than or equal to today, (& subject to other conditions) where something happens.
- The 6 tells the function to ignore errors. We use errors to our advantage by causing rows we don't want (eg blank in column AO) to result in an error, thus eliminating that row.
Look up the Help on the AGGREGATE function to learn more.

i realised the MM and YYYY bit wasn't meant to be edited I changed that back but forgot to say, and thank you for all the help I won't know fully if this does what I want it too until july but anyway I can just do an = a different cell for the prior months if it doesn't work so only a couple of seconds anyway so this is perfect and makes my life a lot easier either way.

Much thanks

 

[Peter_SSs]

.. makes my life a lot easier either way.

Much thanks

You are welcome.

 

[PGD15]

Hi, me again.

Is there a way to set the formula up so when the month changes it still only looks at June's data this way I can adapt the formula for each month.

If i am right this should 100% do everything I require it to do.

-_- hate formulas with dates lol

 

[Peter_SSs]

Is there a way to set the formula up so when the month changes it still only looks at June's data this way I can adapt the formula for each month.

So the formula would have nothing to do with the date in cell A1?

In that case, how would we know which month (& year) was the "month of interest"?

 

[PGD15]

Month are listed in column b with the year in the formatt - Jun-18 etc... so as a date its 01/06/2018 so this is why it gets confusing

coz I want to use something like edate(a1,1) < then the formula but that doesn't quite work (as i've clearly done something wrong)

 

[Peter_SSs]

Sorry, I don't understand that. Perhaps you had better show a small set of dummy sample data, with the expected results & any further explanation you can to help clarify (per my suggestion in post #10 )

 

[PGD15]

So to word the formula ... this formula is being written in cell AO8 of the monthly summary tab (this might have changed since yesterday).

I want to know if the month in the monthly summary tab is equal to the month in the daily breakdown tab (both column B in their tab's) then I want to find the most recent data entry for that month that is not 0.

So today 14/06 has a value but tomorrow doesn't so I want to know todays value (from daily breakdown AO column) regardless if its larger or smaller than the than the previous day provided it is from the relevant month.

... also how do I go about showing a sample as I cannot attach a file