Posts by bosco_yip


Thank you it's working perfectly
and one more help if cell have 3 or 4 mobile number how to extract with this formula
One post one question.
Please open a new post for your new question, together with few lines of example data and the expected result.





Try,
Assume data put in cell A1 and A2.
In B2 formula copied down :
=LEFT(A1,FIND("@",SUBSTITUTE(A1,"","@",5))1)

Try,
=SUMPRODUCT((0+LEFT(Sheet1!B2:B27,FIND(" ",Sheet1!B2:B27)1)>7)* Sheet1!D2:D27)
Or,
=SUMPRODUCT((0+LEFT(Sheet1!B2:B27,FIND(" ",Sheet1!B2:B27)1)>30)* Sheet1!D2:D27)

If the Date column is dragged down, this formula will extract the last cell content. In fact, I want the date value in the same row.
Or, try to use INDIRECT function :
=INDIRECT("JCB!B"&MATCH(9^9,OFFSET(INDEX(JCB!1:1,INDEX(MATCH(Display!$I$3&Summary!$M$8&Summary!$M$6,JCB!1:1&JCB!2:2&JCB!3:3,0),0)),,,60000)))

.. to extract one more value from the second column cell (B1134)..........................
To extract B1134 date value in Offset from the cell number AP1134 :
=LOOKUP(9^9,OFFSET(INDEX(JCB!1:1,INDEX(MATCH(Display!$I$3&Summary!$M$8&Summary!$M$6,JCB!1:1&JCB!2:2&JCB!3:3,0),0)),,2INDEX(MATCH(Display!$I$3&Summary!$M$8&Summary!$M$6,JCB!1:1&JCB!2:2&JCB!3:3,0),0),60000))




This is a 2 ways Lookup, using Index + Match function
Try to use this modified formula instead :
=INDEX(source!$B$4:$GZ$521,MATCH($A15,source!$A$4:$A$521,0),MATCH(1,INDEX((L$14=source!$B$3:$GZ$3)*(L$13=source!$B$2:$GZ$2),0),0))
Regards



Try formula solution ,
1] In "Sheet1" E4, formula copied right to F4 and all copied down :
=IFERROR(INDEX(A$4:A$24,AGGREGATE(15,6,ROW($A$1:$A$21)/((TEXT($A$3:$A$23,"0;;0;\0")="0")),ROW($A1))),"")
2] In "Sheet1" G4, array (confirm pressing with Ctrl+Shift+Enter 3 keystrokes altogether) formula copied down :
=IF(F4="","",TEXTJOIN(", ",,IF(Table1_2[Column2]=F4,Table1_2[Column3],"")))

Try to use Index+Match function to return the first entry date, as in :
=IFERROR(INDEX($B$1:$IM$1,MATCH(TRUE,INDEX(B2:IM2<>"",0),0)),"")
Regards


Try,
1] "RF Last 5 Games", in cell J2 CSE formula copied down :
=AVERAGE(INDEX(IF($A$2:$A$866=I2,$C$2:$C$866,0)+IF($B$2:$B$866=I2,$D$2:$D$866,0),N(IF(1,AGGREGATE(14,6,ROW(A$2:A$866)ROW(A$1)/(IF($A$2:$A$866=I2,$C$2:$C$866,0)+IF($B$2:$B$866=I2,$D$2:$D$866,0)),ROW($A$1:$A$5))))))
2] "RA Last 5 Games", in cell K2 CSE formula copied down :
=AVERAGE(INDEX(IF($A$2:$A$866=I2,$D$2:$D$866,0)+IF($B$2:$B$866=I2,$C$2:$C$866,0),N(IF(1,AGGREGATE(14,6,ROW(A$2:A$866)ROW(A$1)/(IF($A$2:$A$866=I2,$D$2:$D$866,0)+IF($B$2:$B$866=I2,$C$2:$C$866,0)),ROW($A$1:$A$5))))))
3] "RF Last 10 Games", in cell L2 CSE formula copied down :
=AVERAGE(INDEX(IF($A$2:$A$866=I2,$C$2:$C$866,0)+IF($B$2:$B$866=I2,$D$2:$D$866,0),N(IF(1,AGGREGATE(14,6,ROW(A$2:A$866)ROW(A$1)/(IF($A$2:$A$866=I2,$C$2:$C$866,0)+IF($B$2:$B$866=I2,$D$2:$D$866,0)),ROW($A$1:$A$10))))))
4] "RA Last10 Games", in cell M2 CSE formula copied down :
=AVERAGE(INDEX(IF($A$2:$A$866=I2,$D$2:$D$866,0)+IF($B$2:$B$866=I2,$C$2:$C$866,0),N(IF(1,AGGREGATE(14,6,ROW(A$2:A$866)ROW(A$1)/(IF($A$2:$A$866=I2,$D$2:$D$866,0)+IF($B$2:$B$866=I2,$C$2:$C$866,0)),ROW($A$1:$A$10))))))
Attached file :