excel - How to make a XLOOKUP for values in groups? - Stack Overflow

admin管理员组

文章数量:1279158

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.

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

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.

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

• excel
• excel-formula

Share Improve this question Follow asked Feb 23 at 20:25 Rasec MalkicRasec Malkic 69533 silver badges1111 bronze badges

Add a comment  | 

3 Answers 3

Sorted by: Reset to default 2

Another possibility, based on the sample data provided:

=LET(
   inc, LAMBDA(v,a,ISNUMBER(MATCH(v,a,0))),
   pvt, PIVOTBY(A1:A18,B1:B18,C1:C18,SINGLE,1,0,,0,,inc(B1:B18,{"TA";"X Y"})*inc(A1:A18,{"DEF";"JKL"})),
   x_y, TAKE(pvt,,-1),
   HSTACK(DROP(pvt,,-1),TEXTBEFORE(x_y," "),TEXTAFTER(x_y," "))
)

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

---

• 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),""))

---

=LET(u,UNIQUE(A2:A18),
     L,LAMBDA(x,FILTER(C2:C18,B2:B18=x)),
VSTACK({"Name","TA","X","Y"},
       HSTACK(u,
              L("TA"),
              --TEXTSPLIT(TEXTAFTER(" "&L("X Y")," ",{1,2})," "))))

This formula expects each name to both have a "TA" and "X Y" value, like in your example. This spills it for each name including headers

本文标签： excelHow to make a XLOOKUP for values in groupsStack Overflow