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.

 

[James006]

Hi,

From your description, it would appear a formula ... even an array formula ... will not be able to handle your situation ...:wink:

Apparently, you are dealing with monthly worksheets within your workbook ... in my opinion, you should consider a macro ...

HTH

 

[PGD15]

Hi,

From your description, it would appear a formula ... even an array formula ... will not be able to handle your situation ...:wink:

Apparently, you are dealing with monthly worksheets within your workbook ... in my opinion, you should consider a macro ...

HTH

I was trying to avoid using a macro as my spreadsheet is already massive and take about 5 mins to write a simply if formula with all the **** lagging :'(

I'll have to try a really complicated if, index, match formula but thanks nonetheless

 

[Peter_SSs]

It is a bit hard to be sure of your sheet names, ranges etc, but see if this gets you anywhere close (after adjusting the sheet name & ranges to suit your data).

=AGGREGATE(14,6,'tab 2'!$S$2:$S$32/('tab 2'!$A$2:$A$32=A2),1)

 

[PGD15]

It is a bit hard to be sure of your sheet names, ranges etc, but see if this gets you anywhere close (after adjusting the sheet name & ranges to suit your data).

=AGGREGATE(14,6,'tab 2'!$S$2:$S$32/('tab 2'!$A$2:$A$32=A2),1)

Think i edited the formula wrong got an error message,

so in cell AM 8 (monthly summary tab) I want to write the formula saying if todays date (cell A1, same tab) is in the range in 'Daily breakdown' between B3:B1048576 then return the value equal too todays date from the daily breakdown tab from range AR3:AR1048576. If this condition is not satisified I would like to repeat using =today()-1 then the rest being the same.

Hope this is clear

 

[Peter_SSs]

Hope this is clear

It is certainly different to how I had interpreted your original description.

1. In column B of 'Daily breakdown'
- Are the dates arranged in any sort of order? If so, what?
- Can a date occur more than once? If so, which should we choose if today's date occurred twice, both with different values in column AR?

2. Do you really have anything like 1,000,000+ rows of data? If you do, and you have many such formulas in your sheet, the recalculation performance of the sheet might be poor. Can you give a lower number that will still be sufficient to 'cover' the data in 'Daily breakdown'?

3. Will this formula in AM8 be copied down and/or across? If so ..
- which way(s) will it be copied?
- will it still reference the same ranges in 'Daily breakdown'? If not, please clarify.
- what cell(s) would it reference, if different, instead of A1?

 

[PGD15]

It is certainly different to how I had interpreted your original description.

1. In column B of 'Daily breakdown'
- Are the dates arranged in any sort of order? If so, what?
- Can a date occur more than once? If so, which should we choose if today's date occurred twice, both with different values in column AR?

2. Do you really have anything like 1,000,000+ rows of data? If you do, and you have many such formulas in your sheet, the recalculation performance of the sheet might be poor. Can you give a lower number that will still be sufficient to 'cover' the data in 'Daily breakdown'?

3. Will this formula in AM8 be copied down and/or across? If so ..
- which way(s) will it be copied?
- will it still reference the same ranges in 'Daily breakdown'? If not, please clarify.
- what cell(s) would it reference, if different, instead of A1?

1:
- descending - long format done in work days
- dates cannot occur more than once as everything is in long format and the dates are work days

2:
- answer simply no.... but this database is evoltutional so i covered all the cells so I wouldn't have to update any formulas but if i was choose like 10,000 rows that would be fine for a couple more years.

3:
- this shall be coppied downwards
- ranges shall not change
- a1 is currently just todays date only thing in cell a1 is the =today() formula, so nothing.

 

[Peter_SSs]

2:
- answer simply no.... but this database is evoltutional so i covered all the cells so I wouldn't have to update any formulas but if i was choose like 10,000 rows that would be fine for a couple more years.
Hmm, 10,000 rows of non-repeating work days only. That's around 40 years worth of dates. Seems like a lot to me!
(At least it is better than the 4,000+ years you allowed for before. )

3:
- this shall be coppied downwards
- ranges shall not change
- a1 is currently just todays date only thing in cell a1 is the =today() formula, so nothing.
If none of the ranges change and you copy the formula down, won't every cell return exactly the same result?

Comments above in red but never-the-less see if this is closer to the mark.

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

 

[PGD15]

Comments above in red but never-the-less see if this is closer to the mark.

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

Let's not questions the validity of my poor maths i mean like 3,650 to allow for approx 10 years, and I think i confused myself a bit when typing out the requirements :')
this works, but i think i said to match the wrong values hence why when dragging it down it pulls the same value. (works for the current month nonetheless so good start :')

In both tabs i have a month, column A (in daily breakdown) and B (in monthly summary) i want to make sure it only pulls the information from the relevant month (in monthly summary range is B3:B14) (daily tab, a3:A10000 - keeping to the number so not to have to change everything)

once that criteria is satisfied then the rest of the formula should be repeated (or maybe the other way round not too sure how this one works)

summary -
I want to return the value in column AO (i removed a couple of columns - stick to AR if you wish i can edit that) if the value is the most recent value (closest too today) but has to be from the correct month too. so when it gets to july i dont pull in July's value into June's field.

I pray that helps it make sense never done anything this complicated normally i know what im doing hahaha

 

[Peter_SSs]

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))