From the source data table
| Name1 | Name2 | ID |
|---|---|---|
| Wanda | Al | 1 |
| Olin | Normand | 2 |
| Harriet | Brock | 3 |
| Harriet | Brock | 16 |
| Wanda | Al | 14 |
| Olin | Normand | 15 |
i would like to take each row with the first occurrence of Name1 (E2:G4):
Formulas:
E2 =UNIQUE(A2:A7)
F2 and fill down =XLOOKUP(E2;$A$2:$A$7;$B$2:$C$7)
As we can see, XLOOKUP perfectly works and returns the row from the source range. But it doesn’t work similar in a single formula as depicted above for I2. In this case it returns the first column only.
Is any way to enforce it to work in a single formula?
Is any explanation of such XLOOKUP behavior?
P. S. This question is more about XLOOKUP behavior then the solution for this particular usage case. Any guess how XLOOKUP works is welcome. It will help to understand the general approach.
10
Not a sophisticated but working formula
=LET(nam,UNIQUE(A2:A7),
ca,VLOOKUP(nam,A2:C7,2,FALSE),
cb,VLOOKUP(nam,A2:C7,3,FALSE),
HSTACK(nam,ca,cb))
VLOOKUP can be changed to XLOOKUP if needed.
Ref. to your comment:
=LET(nam,UNIQUE(A2:A7),
ca,MATCH(nam,A2:A7,0),
cb,CHOOSEROWS(B2:C7,ca),
HSTACK(nam,cb))
4