XLOOKUP/INDEX MATCH spills down but not right

[narnian_uk]

I have data in cells D6:D819. In cell A6, I have this formula:

=XLOOKUP(D6:D819, tbl[ID], tbl[[Forename]:[Preferred name]])

"tbl" comprises of three columns: "Forename", "Surname", and "Preferred name". The formula will produce a column of forenames only (whereas I want it to produce all three columns). If, on the other hand, I place one formula in A6, like this:

=XLOOKUP(D6, tbl[ID], tbl[[Forename]:[Preferred name]],{"","",""})

and then drag down to A819, the forename, surname, and preferred name will be produced in each case (which is what I was trying to achieve with the first formula, without the need for a separate formula in each cell). I tried doing much the same thing with INDEX MATCH and had the same problem. Any help gratefully received!

 

[Fluff]

How about

=index(tbl[[Forename]:[Preferred name]],xmatch(d6:d819,tbl[Student Number]),{1,2,3})

 

[narnian_uk]

How about

=index(tbl[[Forename]:[Preferred name]],xmatch(d6:d819,tbl[Student Number]),{1,2,3})

Thank you. That's perfect.

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Alex Blakenburg]

Another option:

=FILTER(tbl[[Forename]:[preferred name]],ISNUMBER(MATCH(tbl[ID],D6:D819,0)),"")