1

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:

enter image description here

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!

TheMaster
  • 45,448
  • 6
  • 62
  • 85
user6343282
  • 31
  • 6
  • 2
    Just added the sample in table format – user6343282 Sep 29 '22 at 16:53
  • [Tables](https://webapps.stackexchange.com/a/161855/) are a better alternative than spreadsheets to show your data structure. If you share spreadsheets, make sure to also add images of your sheet to avoid closure of your question, as questions here must be [self](https://meta.stackoverflow.com/a/260455) [contained](https://meta.stackexchange.com/a/149892). [Your email address can also be accessed by the public](https://meta.stackoverflow.com/questions/394304/), when you share Google files. – TheMaster Sep 29 '22 at 17:14
  • Thanks for the suggestions. player0's solution below is the one I tried and it worked for me. I upvoted that one :) – user6343282 Sep 29 '22 at 21:50

3 Answers3

3

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
TheMaster
  • 45,448
  • 6
  • 62
  • 85
  • To complete the formula, adding an `IFERROR` would take care of possible empty cells. – marikamitsos Sep 29 '22 at 17:15
  • @marikamitsos I don't do that, unless necessary for chaining formula logic. If there are errors, I want them to show :) – TheMaster Sep 29 '22 at 17:17
  • Fair enough. I just think that errors produced by empty cells are not a necessity and could in general be *"hidden"*. – marikamitsos Sep 29 '22 at 17:20
  • @marikamitsos I don't think empty cells should even be referenced. I'm [not a fan of open ended ranges either](https://stackoverflow.com/a/46884012/), where it seems like nothing is in the cells, but something is. – TheMaster Sep 29 '22 at 17:23
  • 1
    I wasn't thinking of open ranges. Simply referring to a situation where say cell `F2` was empty. My original thought was that column `J` would be created using `=FLATTEN(B2:H3)`. In this case if `F2` would be empty, one would not end up with a *"solid"* column as presented in the example. Errors would interrupt the results. – marikamitsos Sep 29 '22 at 17:30
  • @marikamitsos Fair enough. But, IMO, `N/A` is a better status than a empty cell, as it clearly shows that it is not available rather than hiding any other error. – TheMaster Sep 29 '22 at 17:45
1

try:

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

enter image description here

player0
  • 124,011
  • 12
  • 67
  • 124
0

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)))))

enter image description here

Osm
  • 2,699
  • 2
  • 4
  • 26
  • Hi Osm, the value 123 (not 456) should be filled out in cell L4. Value 123 should be filled out for cells L3-L10 and 456 for cells L11-L13 – user6343282 Sep 29 '22 at 16:02