Horizontal Lookup using Index Match on Google Sheets-CodePudding

For the following image from Google Sheets, I want to fill out column L with the value found in column A "PPID" based on values in column J "ID" and referencing the range columns B-I. For example, the value "123" should be filled out in column L for the IDs in column J that are 981296581, 682975586, etc. I tried using Index-Match formula, such as =INDEX($A$3, MATCH(J3,B3, 0)), but it only worked for the first row of data and showed an error for the remaining rows

Here is the screenshot of my Google Sheets, as well as the tables below it if you need to copy it into your Google Sheets:

SHEET 1

PPID	ID	ID	ID	ID	ID	ID	ID	ID
123	981296581	682975586	6144233	6140531	6047231	4540543	134	34
456	815220681	1532839	1141531

SHEET 2

ID	INTENDED RESULT	COLUMN TO FILL OUT (with formula)
981296581	123
682975586	123
6144233	123
6140531	123
6047231	123
4540543	123
134	123
34	123
815220681	456
1532839	456
1141531	456

Thanks!

CodePudding user response：

The format isn't really conducive for a lookup. We can FLATTEN the key range and "repeat and flatten" the value range(with IF) to make it conducive for any lookup.

=ARRAYFORMULA(
  XLOOKUP(
    K2:K12,
    FLATTEN(B2:I3),
    FLATTEN(IF(COLUMN(B2:I3),A2:A3,))
  )
)

ID(K1)	INTENDED RESULT	COLUMN TO FILL OUT (with formula)
981296581	123	123
682975586	123	123
6144233	123	123
6140531	123	123
6047231	123	123
4540543	123	123
134	123	123
34	123	123
815220681	456	456
1532839	456	456
1141531	456	456

CodePudding user response：

Use this

=ArrayFormula(IF(A3="",,
 BYROW(B3:I3, LAMBDA(l, IF(ARRAY_CONSTRAIN(QUERY(
 TRANSPOSE(REGEXMATCH(J3&"",l&"")), "Select Col1 where Col1 = TRUE"),1,1)<>TRUE,,A3)))))

CodePudding user response：

try:

=INDEX(IFERROR(VLOOKUP(J3:J, IFERROR(SPLIT(FLATTEN(
 IF(B3:I="",,B3:I&"×"&A3:A)), "×")), 2, 0)))