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!

 

[SanjayGMusafir]

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?

Lookup array has to be a particular column on worksheet 1

OR

Multiple conditions to search multiple columns

OR

Do you want to tell excel that it could be anywhere in 116 Columns on Worksheet 1?

 

[Ozzy23]

Do you want to tell excel that it could be anywhere in 116 Columns on Worksheet 1?

This as the value I'm searching for could be in any of the 116 columns

Attaching a screenshot of the basic scenario to help visualize.

 

[Fluff]

Hi & welcome to MrExcel.
You mention 116 columns but cols B to GI is 190, so which should it be?

 

[Ozzy23]

Hi & welcome to MrExcel.
You mention 116 columns but cols B to GI is 190, so which should it be?

Thanks for the welcome. It's 116 columns, the screenshot is just a sample for reference of how I have the sheets setup etc.

 

[RoryA]

Could the lookup value appear more than once in those columns? If so, what outcome do you want?

 

[Fluff]

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.

 

[Ozzy23]

Could the lookup value appear more than once in those columns? If so, what outcome do you want?

In some instances yes the same value it could appear more than once in a different column and row within those columns.

Ideally I want to get the return values from column A in worksheet 2 that correspond to the rows my lookup values appear in. There's 116 columns and 726 rows.

I hope I'm making it clear, thanks for helping.

 

[RoryA]

Ideally I want to get the return values from column A

In what format?

 

[Ozzy23]

In what format?

Just as the same values that are populated in the column. Eg. APP-1234