Index Match Horizontally and Vertically

Question

I am trying to create a formula in which I can search the sheet 'Judges' Assignments' to see what room a judge is assigned to and then return that value in the column next to the judge's name in the sheet 'All Judges.' I have tried index-match and hlookup formulas, but I cannot anything to work. Thank you so much for any help! I have an accessible view-only copy of the sheet at https://docs.google.com/spreadsheets/d/1YJH-d7MtYcVd6EC06TVvmq5XP8bTRhwAVzTcxmvmKnE/edit?usp=sharing.

Answer 1

Use vlookup() with an { array expression }, like this:

=arrayformula( 
  iferror( 
    vlookup( 
      A2:A, 
      { 
        'Judges'' Assignments'!B2:B, 'Judges'' Assignments'!A2:A; 
        'Judges'' Assignments'!E2:E, 'Judges'' Assignments'!A2:A; 
        'Judges'' Assignments'!H2:H, 'Judges'' Assignments'!A2:A 
      }, 
      2, false 
    ) 
  ) 
)

See vlookup() help page.

Answer 2

Formula

The formula creates an array of NULL values and room names assigned to the judge. Then TEXTJOIN converts the array to a single comma delimited text string while ignoring blank values. While it is expected that a judge will only be assigned to one room, if more than one room is assigned by design or by error, they will al be listed in the results.

=BYROW(judgelist, LAMBDA(judge, IF(judge<>"", 
  TEXTJOIN(", ", TRUE, BYROW(assignments, LAMBDA(_row, 
    IF(INDEX(_row,,1)<>"",
       IFERROR(IF(MATCH(judge,_row,0),INDEX(_row,,1),)),)
    ))),
)))

Named Ranges

Name	Reference	Notes
judgelist	'All Judges'!A2:A500	Judge lookup list
assignments	'Judges'' Assignments'!A2:J500	Assignment date (rooms, judges etc.)

Google Function Support

BYROW	LAMBDA	TEXTJOIN	IF
INDEX	IFERROR	MATCH