# Index Match Horizontally and Vertically

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.

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
)
)
)
``````
• Your solutions are always simpler and more elegant. Mar 7 at 18:04

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