Forum Discussion
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, it’s fed from a Microsoft form so is a nightmare that I can’t adjust.
I am trying to find a function to do top 3 prioritise for each area where it will initially search 2 columns with if and the third with this/or and with these parameters in place search the 4th column see which has the highest priority rating. (This will be displayed in cell a1 for arguements sake) then in a2 I would use the same functions tied into xlookup/index.match and be able to pull the task name, a3 the doer, a4 the status
problem is at the minute I can’t even get it to index/match if there if more than on If in the function. It ignores the second IF function
my current attempt is =INDEX(B:B,MATCH(LARGE(IF(D:D=“Sector”,IF(E:E=“Sub Sector”,F:F)),1),F:F,0))
where B:B = task name
D:D = Sector names
E:E = Sub Sector Names
F:F = Priority Score
V:V = Job Status (not above but required below)
so ultimately what I want to be able to do is create a function that shows that if D:D has a specific name, and E:E also has a specific name, and V:V has names “active”, “on hold” OR “unassigned” then from the remaining “list” automatically on F:F search the top 3 scorers (or top scorer, 2nd, then 3rd by changing the parameter of large) and from which using xlookup/index.match to pull the task name from B:B
so I mean, quite an easy one really…… I don’t think I’m asking for much
thank you to anyone who even read all of that as I appreciate its a lot.
if anyone can off any suggestions I would be hugely grateful
=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.
1 Reply
=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.