Index match

Question

In the attached excel, want to get the non-null value corresponding to the latest date and earliest date for each product under columns H & I in sheet Data 2, looking up dates from sheet 1 corresponding to the product match in headers.

Sheet Data

Sheet Data 2

Answer 1

Hi,

This M code in Power Query works as well

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type datetime}, {"Product A", Int64.Type}, {"Product B", Int64.Type}, {"Product C", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Date"}, "Attribute", "Value"),
    #"Grouped Rows" = Table.Group(#"Unpivoted Other Columns", {"Attribute"}, {{"Value for earliest date", each Table.Min(_,"Date")},{"Value for farthest date", each Table.Max(_,"Date")}}),
    #"Expanded Value for earliest date" = Table.ExpandRecordColumn(#"Grouped Rows", "Value for earliest date", {"Value"}, {"Value"}),
    #"Expanded Value for farthest date" = Table.ExpandRecordColumn(#"Expanded Value for earliest date", "Value for farthest date", {"Value"}, {"Value.1"})
in
    #"Expanded Value for farthest date"

Answer 2

Hi,

Enter this formula in cell F1

=LET(c,LET(h,B1:D1,d,A2:A11,l,HSTACK(TOCOL(IF(SEQUENCE(,COLUMNS(h)),d)),TOCOL(IF(SEQUENCE(ROWS(d)),h)),TOCOL(B2:D11)),SORT(FILTER(l,CHOOSECOLS(l,3)<>0),2,1)),GROUPBY(CHOOSECOLS(c,2),CHOOSECOLS(c,3),HSTACK(LAMBDA(a,INDEX(a,1,1)),LAMBDA(b,INDEX(b,COUNTA(b),1))),,0))

Hope this helps.

Answer 3

Hi,

Use below formula in cell G1 of Sheet2:

=LET(data, Sheet1!A1:D11, d, DROP(VSTACK(TAKE(data, 1), SORT(DROP(data, 1))),, 1), ts, --TEXTSPLIT(TEXTJOIN(";",, IFERROR(BYCOL(DROP(d, 1), LAMBDA(a, ARRAYTOTEXT(TOCOL(IF(a = "", "", a), 1)))), "")), ", ", ";"), VSTACK({"Product","Value for Earliest Date","Value for Latest Date"}, IFERROR(HSTACK(TRANSPOSE(TAKE(d, 1)), TAKE(ts,, 1), BYROW(ts, LAMBDA(b, TAKE(TOROW(b, 3),, -1)))), "")))

Hope this helps.

IlirU

Answer 4

Hello Janz,

Thank you for reaching out with your question about using Index Match in Excel to return the non-null values for the latest and earliest dates for each product I understand how important it is to get accurate results when working with multiple sheets and product data and I can see how this task might feel a bit complex at first.

The reason this happens is because Index Match by itself will not automatically filter for earliest or latest dates so you need to combine it with functions like MIN or MAX to identify the date boundaries and then use Index Match to return the corresponding non-null value. The resolution is to apply a formula where MAX or MIN finds the latest or earliest date in Sheet 1 for the product and then Index Match retrieves the value linked to that date into columns H and I in Sheet Data 2.

Would you like me to show you the exact formula structure for both earliest and latest date lookups so you can apply it directly to your workbook?

I hope this answer is helpful if you have further questions feel free to reply back

Regards,

Marcelo