# Posts by NBVC

Important Notice

Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

• ## Sorting data in a table in a single column

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

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

• ## Payment date and paythrough date formula

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.

• ## Converting text value to time

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"}))

• ## Two-Way Lookup with wildcard (\$35 USD)

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.

• ## XLOOKUP #VALUE Error :-(

Maybe?

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

• ## Formula to find and match an exact word in a cell

Maybe you want?

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

• ## Offset incremental row increase away from anchor row

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

• ## Returning 10 largest values when looking up in a list!

See attached.

I double-checked and it looks correct.

• ## Returning 10 largest values when looking up in a list!

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

• ## Returning 10 largest values when looking up in a list!

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

• ## Returning 10 largest values when looking up in a list!

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.

• ## Returning 10 largest values when looking up in a list!

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.

• ## I need to reference cells in a row on one sheet as a column in another

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.

• ## Finding the Median for certain dates from a list of data

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

• ## Finding the Median for certain dates from a list of data

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

• ## Finding the Median for certain dates from a list of data

Don't see any attachment.

• ## Finding the Median for certain dates from a list of data

Did you confirm it with CTRL+SHIFT+ENTER?

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

• ## Finding the Median for certain dates from a list of data

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.

• ## Vertical lookup when the range changes.

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)