1

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.

1

2 Answers 2

2

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.

1
  • 1
    Your solutions are always simpler and more elegant.
    – Blindspots
    Mar 7, 2023 at 18:04
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.