I have a table C3:E6 as follows.

Given H3:H4, I would like to get their corresponding columns in C3:E6.

I tried in N3 the formula =XLOOKUP(H3:H4,C3:C6,D3:E6,,0), it returned only the first corresponding column rather than 2 columns.

Does anyone know how to write only one array formula covering several rows to return all the corresponding columns?

PS: I would prefer solutions without VBA and without LAMBDA function, which is still in preview mode.

CodePudding user response：

You could use the following: =INDEX(D3:E6,MATCH(H3:H4,C3:C6,0),SEQUENCE(1,2))

Or if it may contain blank values and don't want them displayed as null: =SUBSTITUTE(INDEX(D3:E6,MATCH(H3:H4,C3:C6,0),SEQUENCE(1,2)),"","")