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.

 

[Peter_SSs]

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

Read post #10 .

 

[PGD15]

Hi Peter,

Apologies for the delay got roped into some other work. I've got the screenshots but it wont let me attach them, is it possible to private message them to you, i've completed the entire document other than this formula (probably down to my awful wording of my problem)

 

[PGD15]

or i can re-word my problem to make sense :') (i hope)

So i have a monthly summary tab. where i want to know the values from the daily breakdown for last data entry within that given month that is not equal to zero. but the daily values vary so Min & Max don't work.

cell ranges my vary to what i used before but below either is fine as i need this in mutplie cells but I can adapt once it works once.

so in cell AB21 (monthly tab) this refers to june-18 I want to know the latest value from the daily tab range (AC3:AC1000) for the month June.

However column ac has all the information for the field i want to know regardless of the month so if it selects the latest value entered when the month turn to july this will pick up July's value, which i don't want in AB21 - so i have the month in cell b21 of the monthly summary tab and want to match this to the daily breakdown column a3:a1000 so it brings back the latest entry if the month is what I want to look at.

I dont think I can word it better than this :')


summary:
i need to return the most recent the value (not largest or smallest) in column AC (daily breakdown tab) provided the month in column A (daily breakdown tab) is equal to the month in cell B21 (monthly summary tab).

 

[Peter_SSs]

I've got the screenshots but it wont let me attach them ..

You don't "attach" the screen shot(s). Post #3 in the 'Look here' link from my signature explains how to use the MrExcel HTML Maker if you read right through it.

is it possible to private message them to you

I'm afraid not - #4 of the Forum Rules prohibits that.

 

[PGD15]

I followed it but got a bit confused :')

I have attached two links to the screenshots of my work file (values and headings not actual but give an example)

https://ibb.co/dQcen8 (monthly tab)
https://ibb.co/nQ8fuo (daily tab)

So in the monthly tab selected cell you can see the fomula I am currently using which return the bottom value in column AC (daily tab) however this value is 0 so I want to know if the value in B21 (monthly tab) is equal to that in column a (daily tab) then return the bottom value that is not 0 from column AC (daily tab)

Dummy data below to help illustrate what I mean I want the value in the green cell to be returned not the yellow cell as its equal to 0.

https://postimg.cc/image/3jz0qw3y9/

 

[PGD15]

Hey Peter,

I solved my problem it was so basic! (i probably over complicated it)

solution below:
=IFERROR(INDEX('Daily Breakdown'!$AC$3:$AC$154,MATCH(0&$B21,'Daily Breakdown'!$AC$3:$AC$154&'Daily Breakdown'!$A$3:$A$154,0)-1),VLOOKUP($B21,'Daily Breakdown'!$A$2:$AV$154,29,TRUE))

Edit:

Forgot to say thanks for the help! Much appreciated

 

[PGD15]

Correct formula is:

I forgot that this would pull back the last day of the prior month so had to add a bit in to prevent that from happening.

=IFERROR(IF(VLOOKUP($B21,'Daily Breakdown'!$A$2:$AV$154,29,TRUE)=0,INDEX('Daily Breakdown'!AC$3:AC$154,MATCH(0&$B21,'Daily Breakdown'!AC$3:AC$154&'Daily Breakdown'!$A$3:$A$154,0)-1),INDEX('Daily Breakdown'!AC$13:AC$154,MATCH('Monthly Summary Tab'!$B21,'Daily Breakdown'!$A$3:$AV$154,0))),"No Data")