Forum Discussion

mbanks's avatar
mbanks
Copper Contributor
Jan 14, 2026

Display Last Value in Column when certain criteria match

Hello all,

I am trying to find a way of displaying the last value in a column when certain criteria match...

Criteria that need to match would be Design, Colour and Size, so the quantity column would populate with the latest stock quantity:

Thanks all!

Michael

excel
Formulas and Functions

3 Replies

  • IlirU's avatar
    IlirU
    Brass Contributor
    Jan 15, 2026

    mbanks​,

    In cell P2 apply the below formula:

    =BYROW(M2:M9 & N2:N9 & O2:O9, LAMBDA(a, TAKE(TOCOL(CHOOSECOLS(D5:K18, XMATCH(TRUE,
 SCAN("", D1:K1, LAMBDA(a,b, IF(b = "", a, b))) & D2:K2 & D3:K3 = a)), 1), -1)))

    It is a dynamic formula and is applied only once. If necessary, you can change the range D5:K18 in the formula.

     

    Hope this helps.

    IlirU

  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Jan 14, 2026
    =INDEX($D$4:$K$50,LARGE(IF(INDEX($D$4:$K$50,,
MATCH(TRUE,$D$1:$K$1&$D$2:$K$2&$D$3:$K$3=O2&P2&Q2,0))<>"",ROW($D$4:$D$50)-3),1),
MATCH(TRUE,$D$1:$K$1&$D$2:$K$2&$D$3:$K$3=O2&P2&Q2,0))

    This formula works in my sample file and in modern and legacy Excel. The formula must be entered as an arrayformula with ctrl+shift+enter if someone doesn't work with Office 365 or Excel for the web or Excel 2024. The formula is in cell R2 and filled down.

     

  • m_tarler's avatar
    m_tarler
    Bronze Contributor
    Jan 14, 2026

    so I assume that first row with style is a merged cell with causes issues with tracking so the formula I created had to reformat that row to have those values repeated for every column.  Here is the formula that would go in D2 of your second image:

    =LET(data,D1:.Z1000,criteria,AA2:AC17,
lineone,TOROW(CHOOSECOLS(TOCOL(TAKE(data,1),3),SEQUENCE(8,,1,0))),
tdata,TRANSPOSE(VSTACK(lineone,DROP(data,1))),
out,BYROW(criteria,LAMBDA(r,SUM((MMULT(--(r=TAKE(tdata,,3)),{1;1;1})=3)*TAKE(tdata,,-1)))),
out)

    where data is that data table starting with the first style in D1 and that .Z1000 should be adjusted to be at least the last column and last row of the data (but can be more as long as the rest is blank as that is what the dot before the Z is for)

    the 'criteria' listed here as AA2:AC17 is the A2:C25 in your second image.  NOTE: you must redo those filters to NOT include column D (where the formula will be)  So you should turn those filters off and then highlight only columns A:C and reclick on filters.

    edit: I'm attaching the file and hope it doesn't make my comment disappear ...

     

Resources