Formula to return last value for X date

[Marklarbear]

Hi All,

Firstly - apologies, I can no longer use xl2bb as IT have changed security settings and it now shows as 'Microsoft has blocked macros from running because the source of this file is untrusted'.

I have columns of values that I update non-frequently for weeks of the year. What I would like to do is build a formula so that on another sheet it returns the last value of data for the corresponding date.

In the attached screenshot, dates are in column B, every so often i would add updated values in the next column across. On another sheet, i would have a list of dates and need the latest value to populate (I have represented this in columns K & L). I'm looking for a formula to put in column L to return the last value for the corresponding date in column K.

Hopefully this all makes sense?

i've tried playing with Xlookup... but with no success....

 

[bebo021999]

In L42:

=LOOKUP(2,1/(INDEX($C$40:$H$65,MATCH(K42,$B$40:$B$65),)<>""),INDEX($C$40:$H$65,MATCH(K42,$B$40:$B$65),))

 

[Marklarbear]

In L42:

=LOOKUP(2,1/(INDEX($C$40:$H$65,MATCH(K42,$B$40:$B$65),)<>""),INDEX($C$40:$H$65,MATCH(K42,$B$40:$B$65),))

works perfectly - Cheers!!!

 

[Peter_SSs]

Given that the data is numeric, you could use this much shorter & more efficient formula.

=LOOKUP(9.9E+307,FILTER(C$40:H$65,B$40:B$65=K42))

I can no longer use xl2bb as IT have changed security settings

You can just copy/paste a range (smallish) from your sheet directly into your post (& identify what columns/rows it is). That way at least we can still copy the data to test with.
Small example where I have hidden cols I:J to keep it a bit smaller

Col/Row	B	C	D	E	F	G	H	K	L
40​	13/06/2022​	503​	837​	466​	620​
41​	20/06/2022​	557​	129​	931​	352​
42​	27/06/2022​	630​	138​	120​	748​			5/09/2022​	854​
43​	4/07/2022​	628​	628​	154​	324​			12/09/2022​	840​
44​	11/07/2022​	164​	170​	494​	361​			19/09/2022​	831​
45​	18/07/2022​	595​	853​	312​	819​			26/09/2022​	973​
46​	25/07/2022​	611​	342​	146​	934​			3/10/2022​	213​
47​	1/08/2022​	939​	880​	907​	771​			10/10/2022​	606​
48​	8/08/2022​	341​	729​	181​	865​			17/10/2022​	932​
49​	15/08/2022​	628​	348​	895​	814​			24/10/2022​	865​
50​	22/08/2022​	971​	596​	394​	557​			31/10/2022​	309​
51​	29/08/2022​	209​	722​	844​	431​			7/11/2022​	439​
52​	5/09/2022​	699​	451​	977​	854​
53​	12/09/2022​	831​	639​	652​	840​

 

[Marklarbear]

9.9E+307,FILTER

works excellent as well!! - and much tidier... Just so I get a complete understanding - how does the '9.9E+307,FILTER' part of the formula work?

 

[MARK858]

'Microsoft has blocked macros from running because the source of this file is untrusted'.

Please see if the below link to a response to a post with the same issue yesterday helps

XL2BB - Excel Range to BBCode

Just been trying XL2BB and works great. Drawback is I'm using European separators. Will they adapt ( to non European settings) when pasting to the board, and the other way round ? (I admit I did not read all the answers in this thread) I pasted from the board to my Excel version and had to do...

www.mrexcel.com

 

[Peter_SSs]

how does the '9.9E+307,FILTER' part of the formula work?

FILTER(C$40:H$65,B$40:B$65=K42)
This part simply returns the row of the data (columns C:H) where the date matches. So for the sample data I posted above, for the formula in cell L42 it would return this (for 5/09/2022)

22 09 05.xlsm
	C	D	E	F	G	H
52	699	451	977	854
Last Value

Then the LOOKUP looks for 9.99E307 which is a very big number (99 followed by 306 zeros) in that range. When LOOKUP cannot find the exact number it is looking for, it returns the last number that it can find. In that case 854.

 

[Marklarbear]

FILTER(C$40:H$65,B$40:B$65=K42)
This part simply returns the row of the data (columns C:H) where the date matches. So for the sample data I posted above, for the formula in cell L42 it would return this (for 5/09/2022)

22 09 05.xlsm
	C	D	E	F	G	H
52	699	451	977	854
Last Value

Then the LOOKUP looks for 9.99E307 which is a very big number (99 followed by 306 zeros) in that range. When LOOKUP cannot find the exact number it is looking for, it returns the last number that it can find. In that case 854.

Cheers Peter - that makes sense in my brain

 

[Peter_SSs]

You're welcome.