Posts by NBVC

You can further use the SORT() function to sort in situ

=SORT(TOCOL(B2:F17,1,1))

Have a look at the WORKDAY() and NETWORKDAYS() functions, they have a [holidays] parameter, which is a list of dates you enter separately and reference in this parameter. Those dates include all special dates you want to skip.

A couple of other ways based on consistency with your sample ....

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1," Days ",":")," Hrs ",":")," Mins ",":")," Secs","")

=TEXTJOIN(":",,TEXTSPLIT(A1,{" Days "," Hrs "," Mins "," Secs"}))

a bit late, but hopefully also useful to others... I think a nested XLOOKUP would work also as XLOOKUP allows for wildcard searches...

=XLOOKUP("*"&I3&"*"&I4,A3:A9&B3:B9,XLOOKUP(I5,C2:F2,C3:F9),"N/A",2)

I placed the "N/A" return assuming that wildcard is typed in and can possibly be a typo.

Maybe?

=XLOOKUP(L10,C4:C34,XLOOKUP(M4&N9,D1:I1&D2:I2,D4:I34,""))

Maybe you want?

=ISNUMBER(SEARCH(" "&B1&" "," "&A1&" "))

You can use ROW(A6)*-1 copied down (assuming starting at -6) or ROWS($A$1:$A6)*-1

See attached.

I double-checked and it looks correct.

Update your formula in Sheet2, H7 to: =IFERROR(VLOOKUP(B7,Sheet3!A1:F41837,3,0),"") copied down.

Then use: =IFERROR(LARGE(IF(Sheet2!$H$7:$H$206="CA",Sheet2!$C$7:$C$206),ROWS($A$1:$A1)),"") in Sheet1

Looks like you have some #N/A errors in column H...

Try: =IFERROR(LARGE(IF(NOT(ISNA(Sheet2!$H$7:$H$206="CA")),Sheet2!$C$7:$C$206),ROWS($A$1:$A1)),"")

confirmed with CSE keys, copied down.

Or use the IFERROR() in the VLOOKUP in column H to return a 0 instead of error, then use my initial formula

The ROWS() function is used to get a consecutively increasing number starting from 1 (for largest), 2 (for second largest), etc...

Did you confirm the formula with CTRL+SHIFT+ENTER keys all hit at the same time? This should show { } brackets around the formula. Then copy it down.

Try something like this:

=IFERROR(LARGE(IF(Sheet1!$H$2:$H$100="CA",Sheet1!$C$2:$C$100),ROWS($A$1:$A1)),"")

Confirmed with CTRL+SHIFT+ENTER, not just ENTER and copied down.

Adjust ranges as necessary.

You can try something like this:

Assuming your items start in E6 and go rightward to H6... then in your new sheet try something like:

=INDEX(Sheet1!$E$6:$H$6,ROWS(Sheet1!A$1:A1))

copied down.

Adjust as necessary.

Check your P range. It needs to be consistent with the F range.

We are not supposed to work with email outside forum. Try saving ad .XLSX and try using attachment tool

Don't see any attachment.

You also have to adjust the ranges to suit your data

Did you confirm it with CTRL+SHIFT+ENTER?

If still can't get it then attach it to this thread

You can add more conditions to the formula...

=MEDIAN(IF(MONTH($F$2:$F$100)=10,IF($P$2:$P$100<30,$P$2:$P$100)))

=MEDIAN(IF(MONTH($F$2:$F$100)=10,IF($P$2:$P$100>=30,IF($P$2:$P$100<60,$P$2:$P$100))))

etc.

Does this work?

=INDEX('Annual Totals'!B8:ED10,SUMPRODUCT((ISNUMBER(SEARCH(HLOOKUP(I5,'Annual Totals'!A5:EB10,(3+Cheque!G5),FALSE),'Annual Totals'!B8:ED10)))*(ROW('Annual Totals'!B8:ED10)-7)),SUMPRODUCT((ISNUMBER(SEARCH(HLOOKUP(I5,'Annual Totals'!A5:EB10,(3+Cheque!G5),FALSE),'Annual Totals'!B8:ED10)))*(COLUMN('Annual Totals'!B8:ED10)-1))+1)