excelexcel-formula

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

0 A B C D E F G H I J K L 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:

  1. The lookup_array will always be the same. In this example Range $H$1:$H$17.
  2. Values in the lookup_array are not unique. In this example 130, 400 and 70.
  3. 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.
  4. 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?

Solution

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

    enter image description here

    =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