IF VLOOKUP LARGE combination

Hello brainstrust,

First time poster so apologise for poor lingo.

Looking for a formula(s) to return the 8 most recent non-blank values from a table where condition 'X' is met.

Have been trying things like (for first, then third most recent ammonia result at location 1):
{=IF(Table1[Sample Location] = 1, VLOOKUP(MAX(Table1[Date]), Table1[#All], 14, FALSE), "")}
{=IF(Table1[Sample Location] = 1, VLOOKUP(LARGE(Table1[Date], 2), Table1[#All], 14, FALSE), "")}

Date is in dd/mm/yyyy format.
Max formula seems to work, but each subsequent rendition of large k is providing me the same output as the max?

Appreciate your help! Please find a small section of the workbook attached.

There are multiple occurrences of 28/05/2021 so LARGE(Data,1), LARGE(date,2) will find the same value i.e 28/05/2021 AND VLOOKUP will simply find the first occurrence of this date so you will get the same value returned.

Thankyou Ali!!! You are a gem.

G'day brainstrust,

Wondering if its possible to build on Ali's formula '=LOOKUP(2,1/((Table1[Sample Location]=$D$3)*(Table1[Date]=LARGE(IF(Table1[Sample Location]=$D$3,Table1[Date]),1))),Table1[Ammonia as N (mg/L)])' to return an average for a sample location within a given date range, e.g. annual average of ammonia for sample location $D$3 for the year 2021.

Alternatively, can the formula be amended to return a single value from within a date range, i.e. ...(Table1[Date]>01/01/2021<31/01/2021) to return the single recorded value from within January 2021.

Appreciate your help.

Jack.

BTW, another option for your original question since you're using 365 is to put this formula in cell D5. It will "SPILL" across the most recent 8 (no need to copy the formula across.

It also allows you to have the same name as the Headers on "Data" tab down the Quality Characteric column so you can just copy down the formula.

Please Login or Register  to view this content.

I've attached a sample.

To get an average over a data range, for a location, for a characteristic, try this (it assumes the "start date" is in cell M3 and "end date" is in cell N3):

Please Login or Register  to view this content.

To display the values recorded within a date range, you could try this (assumes the same cells for the date range):

=INDEX(FILTER(Table1,(Table1[Sample Location]=$D$3)*(Table1[Date]>=$M$3)*(Table1[Date]<=$N$3)),,MATCH($B5,Table1[#Headers],0))

See attached.