SOLVED

Return value depending on numbers in multiple other columns

Copper Contributor

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 Sample File 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.

7 Replies

@MattijsR 

Working with PQ and pivot.

 

@Detlef Lewin 

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

@MattijsR 

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):

 

Patrick2788_0-1687284742392.png

 

 

@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.

best response confirmed by MattijsR (Copper Contributor)
Solution

@MattijsR 

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):

Excel Labs - Microsoft Garage

1 best response

Accepted Solutions
best response confirmed by MattijsR (Copper Contributor)
Solution

@MattijsR 

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):

Excel Labs - Microsoft Garage

View solution in original post