# Posts by bosco_yip

• ## data array with date range and return text value in result

Maybe,

In C2, formula copied down :

=LOOKUP(B2,OFFSET(INDEX(\$I:\$I,MATCH(A2,\$G:\$G,0)),0,0,4,3))

Or try this non-array formula

In A1, formula copied down :

=IFERROR(LOOKUP(1,SEARCH(L\$1:L\$4,B1),M\$1:M\$4),"")

Regards

• ## Large function with criteria.

Try,

In H4, enter formula :

=AGGREGATE(15,6,H9:H30/(D9:D30=D4)/(E9:E30=E4)/(F9:F30=F4)/(G9:G30=G4)/(H9:H30>=C4),1)

Regards

• ## If cell is not empty, copy row...

Maybe try this formula solution

In "SUMMARY" sheet cell A100, enter formula and copied across to F100 :

=IFERROR(INDEX(INVOICES!A:A,AGGREGATE(15,6,ROW(INVOICES!\$A\$4:\$A\$40)/(INVOICES!\$F\$4:\$F\$40<>""),ROW(A1))),"")

And,

Select cell C100:F100 >> Custom Cell Format, enter : #,##0.00;;;

Then,

Select A100:F100, all copied down to row line no. 136

>> Finish

TEST (BY).xlsx

• ## Vlookup formula does not work for some cell

In D8 formula copied down :

=(1+IFERROR(VLOOKUP(VLOOKUP(MONTH(C8),\$H\$8:\$I\$19,2,0) & "-" & YEAR(C8),\$A\$2:\$B\$61,2,0),B\$2))^(1/12)

Regards

• ## dynamic list length to find top 8 from last 20

Extract top 4 from the last 15 in the list (new values will be added to the end of the list)

Assume data in A2:A?

In C2, enter formula and copied down 3 lines :

=LARGE(OFFSET(A\$1,MATCH(9^9,A:A)-1,0,-15),ROW(A1))

• ## Vlookup formula with date conditions

In C9, enter formula :

=LOOKUP(9^9,B3:B5/(A3:A5=A9)/(C3:C5<=B9)/(D3:D5>=B9))

• ## Match Columns across sheet and Return adjacent cell value

Try,

In "Sheet2" E2 formula copied down :

=VLOOKUP(A2,Sheet1!A:B,2,0)

• ## Using sumif on different columns without getting a double/triple sum up

Try,

In F2, enter formula :

=SUMPRODUCT((D2:D20<>"")*(B2:B20<>"")*(C2:C20<>""),A2:A20)

the formula result will return 496

• ## Combining Text Data from multiple rows to one row

Try,

1] In A15, copied right to B15 and all copied down :

=INDEX(A\$4:A\$11,MATCH(0,INDEX(COUNTIF(A\$14:A14,A\$4:A\$11),0),0))

2] In C15, copied across right to AG15 and all copied down :

=INDEX(C\$4:C\$11,AGGREGATE(15,6,ROW(\$B\$4:\$B\$11)-ROW(\$B\$3)/(\$B\$4:\$B\$11=\$B15)/(C\$4:C\$11<>""),1))

• ## MOD(ROWS() Function

Maybe,

1] C5, keep empty

2] In C6, formula copied down to C57 :

=IFERROR(1/(1/(INT((ROW(A1)-1)/D\$5)*F\$5)),"")

Regards

• ## Get Header data and row/column data value

Try.............

1] If you have Office 365 or Excel 2019 CONCAT function, use this in H2 and copied down :

=""""&CONCAT(INDEX(\$A\$1:\$F2,N(IF(1,IF({1,0},1,ROW(A2)))),MATCH(G2,A2:F2,0)+1)&" ")&""""

Or,

2] If you don't have CONCAT function, use this in H2 and copied down :

=""""&OFFSET(\$A2,ROW(A\$1)-ROW(A2),MATCH(G2,A2:F2,0))&" "&OFFSET(\$A2,0,MATCH(G2,A2:F2,0))&""""

• ## Average data by date excluding the first value

Bosco, as I stated in my original post, I cannot use the formula you are proposing for column C. I must calculate the difference between the current and previous value for all data points not just the ones which occur on the same day. The question was how to arrange a formula which will allow me to calculate the daily average of those differences, excluding the first value for each day.

Then,

D2, formula copied down :

=SUMPRODUCT((A\$2:A\$10=A2)*(A\$1:A\$9=A\$2:A\$10)*N(+C\$2:C\$10)/(COUNTIF(A\$2:A\$10,A2)-1))

• ## Average data by date excluding the first value

Try...………………...

1] In C2, formula copied down :

=IF(A2=A1,B2-B1,0)

2] In D2, formula copied down :

=AVERAGEIFS(\$C\$2:\$C\$10,\$C\$2:\$C\$10,"<>0",\$A\$2:\$A\$10,A2)

• ## Use last value in a column, increment it by 1 and start a new column with this value. If value>x, then start the count in this column, from 1

Similar to Fluff's idea, another shorter formula to obtain the same result.

=0+TEXT(LOOKUP(9^9,C\$14:C\$37)+1,"[>11]1")

Regards

Bosco

• ## Extract data fields from wrapped text cell

Assume criteria housed in B1:F1 as per below table

In B2, formula copied across to F2 and all copied down ;

=MID(LEFT(\$A2,IF(C\$1="",250,FIND(C\$1,\$A2)-1)),FIND(B\$1,\$A2)+LEN(B\$1)+1,250)

• ## Vlookup or array formula based on criteria

Further to my posted formula in Post #.2,

Should you wanted to return multiple result in respect of the multiple criteria, you need TEXTJOIN function of which available in Office 365 or above.

Then,

In "Map" sheet F2, array formula (confirm pressing Ctrl+Shift+Enter instead of just Enter) copied down :

=IF(\$G2="Y",TEXTJOIN(", ",1,INDEX(Lookup!\$D\$2:\$G\$52,N(IF(1,MATCH(FILTERXML("<a><b>"&SUBSTITUTE(B2,",","</b><b>")&"</b></a>","//b"),Lookup!\$C\$2:\$C\$56,0))),MATCH(C2,Lookup!\$D\$1:\$G\$1,0))),"")

and,

Trailing space in B12 should be removed

Regards

• ## Vlookup or array formula based on criteria

In "Map" sheet F2, formula copied down :

=IF(\$G2="Y",IFERROR(VLOOKUP(\$B2,Lookup!\$C\$2:\$G\$52,MATCH(C2,Lookup!\$C\$1:\$G\$1,0),0),""),"")

However,

1] Remove trailing space in B12

2] You have multiple value in cells B99, B243 and B248 of which the above formula will return blank

Regards

• ## Indirect/substitute formula help

To choose the options in column C in the "Report" sheet if chosen in Column B

In "Report" sheet, select C2 >> Data Validation >>

>> Allow : List

>> Source : =OFFSET(List!\$B\$19,COUNTIF(List!\$C\$2:\$K\$11,\$B2)+COUNTIF(List!\$L\$2:\$O\$11,\$B2)*2,,,COUNTA(CHOOSE(COUNTIF(List!\$C\$2:\$K\$11,\$B2)+COUNTIF(List!\$L\$2:\$O\$11,\$B2)*2,First_Half,Second_Half)))

>> OK

>> Finish

then,

Copied down