Forum Discussion
Return value depending on numbers in multiple other columns
Hello,
Can anyone help me optimize the following formula in Excel for Office 365:
=IF(INDEX(list2!$D:$D; MATCH(1; COUNTIFS(C3; list2!$E:$E; C5; list2!$F:$F)+COUNTIFS(C3; list2!$G:$G; C5; list2!$H:$H)+COUNTIFS(C3; list2!$I:$I; C5; list2!$J:$J); 0))=INDEX(list2!$D:$D; XMATCH(1; COUNTIFS(C3; list2!$E:$E; C5; list2!$F:$F)+COUNTIFS(C3; list2!$G:$G; C5; list2!$H:$H)+COUNTIFS(C3; list2!$I:$I; C5; list2!$J:$J); 0; -1)); INDEX(list2!$D:$D; MATCH(1; COUNTIFS(C3; list2!$E:$E; C5; list2!$F:$F)+COUNTIFS(C3; list2!$G:$G; C5; list2!$H:$H)+COUNTIFS(C3; list2!$I:$I; C5; list2!$J:$J); 0)); TEXTJOIN(" / "; TRUE; INDEX(list2!$D:$D; MATCH(1; COUNTIFS(C3; list2!$E:$E; C5; list2!$F:$F)+COUNTIFS(C3; list2!$G:$G; C5; list2!$H:$H)+COUNTIFS(C3; list2!$I:$I; C5; list2!$J:$J); 0)); INDEX(list2!$D:$D; XMATCH(1; COUNTIFS(C3; list2!$E:$E; C5; list2!$F:$F)+COUNTIFS(C3; list2!$G:$G; C5; list2!$H:$H)+COUNTIFS(C3; list2!$I:$I; C5; list2!$J:$J); 0; -1))))
The formula is used to search for a number combination on one sheet and then display the corresponding value in another sheet at the number combination.
The combination of numbers can occur several times. Therefore, the MATCH and the reverse MATCH are compared with each other. Unfortunately this gives only the first and last results, no results in the middle (if present, in the https://1drv.ms/x/s!Aqvbpmk22DS9ig9KWbzBbDByggPI?e=QN0Qwk the combination 3 - 6 is present three times).
There can be more than six tables in "list1" (due to performance issues, I removed a few rows of tables).
There may also be more entries in the list in "list2".
I suspect that the five INDEX and five MATCH functions for one cell are the cause of the performance issues. It would be great if someone could help me to optimize the formula to make the file usable.
Or maybe there is a completely different solution to get the same (maybe even better) result.
Thank you,
Matt
PS.
Macro's and VBA can't be used.
I've converted 'data' and 'items' to dynamic named ranges and updated 'GetData' so it will still calculate if an item is missing.
For editing named items and functions, I'd recommend using Advanced Formula Environment (Part of Excel Labs):
7 Replies
This will do it. It's not too bad after the data arrangement is changed so REDUCE can FILTER recursively.
'GetData Lambda =LAMBDA(input,LET( description, DROP(REDUCE("", items, LAMBDA(a, v, VSTACK(a, EXPAND(v, 3, , v)))), 1), NMatrix, WRAPROWS(TOCOL(data), 2), DROP( REDUCE( "", SEQUENCE(20), LAMBDA(a, v, LET( results, FILTER( description, (TAKE(NMatrix, , 1) = input) * (TAKE(NMatrix, , -1) = v), "" ), VSTACK(a, TEXTJOIN(" / ", , results)) ) ) ), 1 ) ))All you have to do at the sheet level is supply the 'input' (Numbers 1 to 6):
Patrick2788that works so well.
Thank you!I've found that I can edit your code in Name Manager. Is this the right place to work on it?
What value should I edit if I want to add columns to the "data"?
If there is an empty cell in the "items" column, then all results in "list1" will return a wrong data type error.
Is there a way to solve this?
If there is not, I can populate that column with temporary data that can be overwritten when the file is used.I've converted 'data' and 'items' to dynamic named ranges and updated 'GetData' so it will still calculate if an item is missing.
For editing named items and functions, I'd recommend using Advanced Formula Environment (Part of Excel Labs):
Your sample file looks very promising.
But is it possible to do it without the "external data source"?
I'm trying to figure out which source is used, but can't find how, being completely new to PQ.
Can you explain to me, step by step, how you created the result?
Thanks,
Matt
