XLOOKUP - Lookup Array Data in Multiple Columns

[Ozzy23]

Hi All,


Really struggling to get around the below problem. Any help would be appreciated.

I have 2 worksheets within my workbook and trying to create a XLOOKUP. See scenario below.


=XLOOKUP(WORKSHEET2(A2), WORKSHEET1(116 COLUMNS), WORKSHEET1(COLUMN A))


Worksheet 2 (A2) = My LOOKUP value is in column A of worksheet 2

Worksheet 1 (116 COLUMN) = My LOOKUP ARRAY is on worksheet 1 and is 116 columns of data. This is where I am struggling as XLOOKUP gives an error as I am assuming it cannot search across columns?

Worksheet 1 (COLUMN A) = The RETURN ARRAY is column A of Worksheet 1 and the value will be populated into my cell in Worksheet 2


Any solutions on how to tackle this would be appreciated if any additional info is required let me know!

 

[RoryA]

I mean if there are multiple matches, what output do you expect? Multiple rows/columns, a delimited list, or something else?

 

[Ozzy23]

I mean if there are multiple matches, what output do you expect? Multiple rows/columns, a delimited list, or something else?

If there's multiple matches of my lookup value, if possible the return values should be side by side in separate columns.

 

[Ozzy23]

116 is B to DM, so how about

=XLOOKUP(A2,TOCOL(Sheet1!$B$2:$DM$100),TOCOL(IF(SEQUENCE(,116),Sheet1!$A$2:$A$100)))

This assumes the lookup values only occurs once in the data.

Thank you will give this a try for another value that I know only has one instance will let you know if it worked for me.

 

[RoryA]

You could use something like:

=TRANSPOSE(FILTER(Sheet2!A1:A100,BYROW(Sheet2!B1:BY100,LAMBDA(r,COUNTIF(r,A2)>0))))

 

[Ozzy23]

You could use something like:

=TRANSPOSE(FILTER(Sheet2!A1:A100,BYROW(Sheet2!B1:BY100,LAMBDA(r,COUNTIF(r,A2)>0))))

Thanks Rory!! I think this has worked but just to ask if the value is found more than once would it create a new column and input this side by side or would I need to create empty columns first?

 

[RoryA]

There will need to be empty columns there already, or you'll get a #SPILL error

 

[Ozzy23]

There will need to be empty columns there already, or you'll get a #SPILL error

The value appears twice for one of the examples. So I've created a few extra columns. Cleared the column of my query and entered the formula again. I still get the spill error and it hasn't populated the columns I've created. Am I doing something wrong?

 

[RoryA]

If you're doing it in a table, it won't work. Dynamic array formulas can't spill in tables.

 

[Ozzy23]

If you're doing it in a table, it won't work. Dynamic array formulas can't spill in tables.

Ah ok so in this situation should I remove the table format, run the formula to populate the columns and then I can reapply the table formatting? Appreciate the help so much, learnt something new