Question

XLOOKUP with flexible column for return_array and not unique values in lookup_array

0	A	C	E	F	H	I	K	M
1	Products	Shop	2023-S	2023-M	2024-S	2024-M
2
3	Product A	Shop3	80	2%	120	22%
4	Product B	Shop1	320	17%	400	15%		Data from Column
5	Product B	Shop3	470	30%	750	8%	2024-S Selected	2024-M
6	Product B	Shop2	500	4%	70	4%	400	15%
7	Product C	Shop2	160	10%	245	10%	400	35%
8	Product D	Shop1	500	8%	130	4%	70	4%
9	Product D	Shop4	130	11%	130	4%	520	42%
10	Product E	Shop2	75	8%	650	15%	130	4%
11	Product E	Shop1	60	47%	90	7%	90	7%
12	Product E	Shop4	500	25%	400	35%	130	4%
13	Product E	Shop3	350	9%	140	13%	130	9%
14	Product F	Shop2	60	30%	130	9%	70	16%
15	Product G	Shop2	90	5%	370	12%
16	Product H	Shop1	390	27%	70	16%
17	Product H	Shop2	70	18%	520	42%

In Range M6:M14 I want to get the corresponding data based on the values in Range K6:14.
However, I want to have the flexibility that the values in Range M6:M14 are selected based on the column header entered in Cell M5.

In the table Column 2024-M is selected.
If I change Cell M5 for example to Column Header Products the Range M6:M14 should look like this:

Product B
Product E
Product B
Product H
Product D
Product E
Product D
Product F
Product H

Summarized, I am looking for something like this:

Range M6:14 =XLOOKUP(K6:K14,$H$1:$H$17,Based on input in Cell $M$5,NA(),0)

Note:

The lookup_array will always be the same. In this example Range $H$1:$H$17.
Values in the lookup_array are not unique. In this example 130, 400 and 70.
If a value in the lookup_array is not unique it will always appear with the exact same frequency in Range M6:M14 as you can see for 130, 400 and 70 in the example.
The solution from this question does not work here because the results in Range M6:M14 should not only be corresponding to the values Column A. It can be any column in the table beased on the Column Header entered in Cell M5.

What formula do I need to make the outcome in Range M6:M14 work based on the selected Column Header in Cell M5?

3 115 3

1 Jan 1970

Solution

Here is what you can do to achieve the desired output, using one single dynamic array formula:

=LET(
     _A, K6:K14,
     _B, H3:H17,
     _RollingA, MAP(_A,LAMBDA(α,COUNTIF(α:K6,α))),
     _RollingB, MAP(_B,LAMBDA(α,COUNTIF(α:H3,α))),
     XLOOKUP(_A&"|"&_RollingA,_B&"|"&_RollingB,XLOOKUP(M5,A1:I1,A3:I17)))

Also, the last question, which is tagged with the present query both are different, even though the subject line stated about flexible column, but in context of the OP it was nowhere mentioned that the output column needs to be flexible, please try to be succinct and clear with your questions.

To explain a bit about the above solution, we are using MAP() function to create a cumulative/rolling/running counts for the respective arrays which have duplicates viz. Selected One and the Lookup array, in order to create a unique sequence or lookup value, to arrive at the desired output.

Or, Using by creating custom LAMBDA():

=LET(
     α, K6:K14,
     δ, H3:H17,
     ƒx, LAMBDA(ε,φ, MAP(ε, LAMBDA(Σ, COUNTIF(φ:Σ,Σ)))),
     XLOOKUP(α&"|"&ƒx(α,K6),δ&"|"&ƒx(δ,H3),
     XLOOKUP(M5,A1:I1,A3:I17,""),""))

Bit similar method applied here by me Display FILTER results in the same order as the values in the criteria range

2024-06-30

Mayukh Bhattacharya

Solution

Formula in M6:

=MAP(K6:K14,LAMBDA(x,@DROP(TOCOL(FILTER(IFS(E3:H17=x,F3:I17),F1:I1=M5),3),COUNTIF(K6:x,x)-1)))

2024-06-30

JvdV

Solution

I will just do it with helper columns, and use formulas step by step:

First in cell N5, input a number 0.

In cell N6, write below formula: =XMATCH(K6,DROP($H$3:$H$17,O6,0),0,1).

In cell O6, write below formula: =SUM(--($K$5:K5=K6)*($N$5:N5))

Drag down the formulas in both column N and column O, you will get this:

Here is what column O does:

e.g cell O7 = 2, meaning row 2 in H3:H17 has value = 400, so in order to find the "next" 400 in H3:H17, we need to drop the first two rows in H3:H17 before we do a XMATCH.

Cell N7 = 8; meaning the 8th row in H5:H17 has value = 400. (we drop the first two rows, so lookup array is H5:H17 instead of H3:H17).

Finally, we get the final results in column P.

The formula in cell P6:

=INDEX(DROP($I$3:$I$17,O6,0),N6,1)

You can drag it down.

If you want to display values based on the header in M5, just change the formula to below: (replace I3:I17 with XLOOKUP($M$5,$A$1:$I$1,$A$3:$I$17))

=INDEX(DROP(XLOOKUP($M$5,$A$1:$I$1,$A$3:$I$17),O6,0),N6,1)

2024-06-30

rachel

Solution

Similar to other methods, i.e. using running frequencies (with SUM):

=LAMBDA(h_, k_, data_range, col_headers, header_for_match,
    LET(
        i_index, XMATCH(header_for_match, col_headers),
        i_, INDEX(data_range, , i_index),
        freq, LAMBDA(arr,
            LET(
                indices, SEQUENCE(ROWS(arr)),
                MAP(
                    indices,
                    LAMBDA(i,
                        SUM(IF(TAKE(arr, i) = INDEX(arr, i, ), 1, 0))
                    )
                )
            )
        ),
        h_freq, h_ & " " & freq(h_),
        k_freq, k_ & " " & freq(k_),
        xlook, XLOOKUP(k_freq, h_freq, i_),
        formatted, IF(
            RIGHT(header_for_match, 2) = "-M",
            TEXT(xlook, "0%"),
            xlook
        ),
        formatted
    )
)(H3:H17, K6:K14, A3:I17, A1:I1, M5)

2024-06-30

nkalvi