Search code examples
excelexcel-formula

How to make a XLOOKUP for values in groups?

I'm trying (in Excel 365) to make a kind of XLOOKUP but more complex.

I have this input table and I'd like to get the TA and X Y codes for each NAME. Then for DEF and JKL then current output I'm geetting and desired output are shown in image below.

enter image description here

My current formula attempt is this:

  • in F2: =XLOOKUP("TA",$B$1:$B$18,$C$1:$C$18,"",0)

  • in G2: =TEXTSPLIT(XLOOKUP("X Y",$B$1:$B$18,$C$1:$C$18,"",0)," ")

but with these formulas I get the first value for TA and X Y and not the corresponding values for DEF and JKL.

How can I do this? Thanks in advance

Input table

NAME CODE VALUE
ABC TA 89
ABC X Y 72 97
ABC MG 50
DEF TA 60
DEF X Y 65 01
DEF MG 31
DEF NC 93
GHI TA 59
GHI X Y 33 56
GHI MG 45
JKL TA 14
JKL X Y 26 70
JKL MG 10
MNO TA 70
MNO X Y 11 22
MNO MG 87
MNO NC 83
Solution

  • Here is one way you could try using the following formula:

    enter image description here

    • Formula used in cell F2, the following spills for the entire array.

    =MAKEARRAY(2,3,LAMBDA(x,y,
 LET(a,LAMBDA(b,
 TEXTSPLIT(TEXTJOIN(" ",1,
 FILTER(b,A2:A18=INDEX(E2:E3,x),""))," ")),
 XLOOKUP(INDEX(F1:H1,y),a(B2:B18),a(C2:C18)," "))))

    Or, you can use the following to copy down for each rows:

    =LET(
     a, LAMBDA(x,TEXTSPLIT(TEXTJOIN(" ",1,FILTER(x,A2:A18=E2,"")),," ")),
     XLOOKUP(F$1:H$1,a(B2:B18),a(C2:C18),""))