Forum Discussion

Steve8140's avatar
Steve8140
Copper Contributor
Jun 30, 2025
Solved

Match/Index or XLookup mixed in with IF and Large

Hello all.  This one has got me beat and I’m really frustrated as I can’t seem to crack it. I have a data sheet with multiple task to be ran across multiple sectors and then multiple sub sectors...
community
excel
Excel on mobile
Formulas and Functions
office 365
  • OliverScheurich's avatar
    OliverScheurich
    Jul 02, 2025
    =TAKE(SORT(
FILTER(
HSTACK(B:B,F:F),
(D:D="Sector")*(E:E="Sub Sector")*((V:V="active")+(V:V="on hold")+(V:V="unassigned"))),
2,-1),
3)

    This formula returns the expected result in my Excel online sample file.

    With FILTER, SORT and TAKE functions INDEX/MATCH and LARGE aren't needed.

    If your data only has a few thousand rows then i'd reference e.g. B1:B50000 instead of B:B in order to increase performance. B:B is the same as B1:B1048576 and references 1048576 rows which can raise performance problems and therefore should be avoided if possible.

OliverScheurich's avatar
OliverScheurich
Gold Contributor
Jul 02, 2025
=TAKE(SORT(
FILTER(
HSTACK(B:B,F:F),
(D:D="Sector")*(E:E="Sub Sector")*((V:V="active")+(V:V="on hold")+(V:V="unassigned"))),
2,-1),
3)

This formula returns the expected result in my Excel online sample file.

With FILTER, SORT and TAKE functions INDEX/MATCH and LARGE aren't needed.

If your data only has a few thousand rows then i'd reference e.g. B1:B50000 instead of B:B in order to increase performance. B:B is the same as B1:B1048576 and references 1048576 rows which can raise performance problems and therefore should be avoided if possible.

Resources