Maybe,
In D1, formula copied down :
=IFERROR(INDEX(A$1:A$7,MOD(ROW(A1)-1,7)+1)&"-"&INDEX(B$1:B$3,INT((ROW(A1)-1)/7)+1),"")
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.
-
In G2, enter formula :
=SUMPRODUCT((E2:E17=F2)/COUNTIFS(E2:E17,E2:E17,A2:A17,A2:A17,C2:C17,C2:C17))
-
B2, copied across right and all copied down :
=MID($A2,FIND(B$1,$A2)+LEN(B$1)+2,10)
-
Then,
Change B1 formula to :
=0+SUBSTITUTE(SUBSTITUTE(LEFT(SUBSTITUTE(MID(A1,FIND("EPS",A1)+4,99)," ",REPT(" ",50)),50),"$",""),"c","%")
and copied down
-
Try,
In B1, formula copied down :
=0+SUBSTITUTE(SUBSTITUTE(MID(A1,FIND("EPS",A1)+4,FIND("vs",A1)-FIND("EPS",A1)-5),"$",""),"c","%")
-
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)),,2-INDEX(MATCH(Display!$I$3&Summary!$M$8&Summary!$M$6,JCB!1:1&JCB!2:2&JCB!3:3,0),0),60000))
-
Try,
C2, array (CSE) entry :
=SUM(IF((A2:A20>0)*MONTH(A2:A20)=B2,1))
or,
D2, normal entry :
=SUMPRODUCT((A2:A20>0)*(MONTH(A2:A20)=B2))
-
Try,
In E2, formula copied down :
=IFERROR(LEFT(A2,FIND("H",A2)-2),)*60-IFERROR(LOOKUP(1,-RIGHT(LEFT(A2,FIND("M",A2)-2),ROW($1:$99))),)
-
In "Summary" sheet C6, formula copied right to D6 and all copied down :
=SUMIFS(INDEX(Data!$D$4:$H$13,,MATCH(C$5,Data!$C$1:$H$1,0)),Data!$A$4:$A$13,$A6,Data!$B$4:$B$13,$B6)
-
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,
In "Sheet1" D2, formula copied down :
=IF(VLOOKUP(B2,Sheet2!B:Y,24,0)=C2,VLOOKUP(B2,Sheet2!B:Y,23,0),"")
-
Try,
In C2, formula copied down :
=IF((MAX(MMULT(--ISNUMBER(SEARCH(MID(A2,COLUMN(INDIRECT("C1:C"&LEN(A2),)),1),B2)),ROW(INDIRECT("1:"&LEN(A2)))^0))-LEN(A2)+LEN(SUBSTITUTE(A2," ","")))/LEN(A2)>0.5,"Match","No Match")
-
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
-
In B2, formula copied down :
=MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&1/17)),99)
-
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 :