Forum Discussion
excel
agnt n l b h Amount 1 1 2 5 2 20 1 2 5 2 20 Total 40 2 1 2 3 2 12 1 2 5 2 20 Total 32 3 1 2 4 2 ...
...I want to return 40 ,100,189...
There's no row with Total = 189 in the data you initially exposed so below result is probably not what you expect:
=LET( Agnt, SCAN(0,Table[agnt], LAMBDA(seed,agnt, IF(ISNUMBER(agnt), agnt, seed)) ) = I1, TRANSPOSE(FILTER(Table[Amount], Agnt * ISNUMBER(XMATCH(Table[h],"total")), "")) )
What more details ? I want to return 40 ,100,189 corresponding to agmt 1 and 32 corresponding to agmt2 and 16 corresponding to agmt3
return 40,32,16,100,180 corresponding to agmt 1,2,3
