Get a list of multiple values in a lookup

In my Excel workbook, I have a sheet testers with 2 columns depicting a list of testers and a third boolean column as email sent flag.

A tester email
B tester name - irrelevant for current issue
C email has been sent or not - irrelevant for current issue

A	B	C
email	name	emailed
`jack@company.com`	Jack	0
`mack@company.com`	Mack	0
`iris@company.com`	Iris B	0

A second sheet allocations has information on which widget has been assigned to which testers:

A widget ID
B email of tester 1 assigned to the widget (data validation from column A of testers sheet)
E email of tester 2 assigned to the widget (data validation from column A of testers sheet)
columns C-D and F-J are not relevant for the current issue.

As shown in the example below, widget 990235 has been assigned to Jack and Mack.

A	B	C	D	E	F	G	H	I	J
widget_id	email1	email1thanked	rating1	email2	email2thanked	rating2	actionsent	difference	average
990235	`jack@company.com`	0		`mack@company.com`	0		0
990236	`mack@company.com`	0		`jack@company.com`	0		0
990231	`jack@company.com`	0		`iris@company.com`	0		0
990197	`iris@company.com`	0		`mack@company.com`	0		0
990003	`mack@company.com`	0		`iris@company.com`	0		0

I am trying (and failing) to figure out how to get a list of all widget_ids allocated to any particular tester.

e.g. for jack@company.com I should get the list "990235, 990236, 990231"

I understand that VLOOKUP is not sufficient as it would find the first match and stop. Besides, my understanding (I may be wrong) is that VLOOKUP matches the first column and the lookup needs to be to the right of it, which is not the case here.

Solution

Put this formula in cell D2 of the testers sheet:

=MAP(
    A2:A4,
    LAMBDA(φ, TEXTJOIN(", ", , IF(allocations!B2:E6 = φ, allocations!A2:A6, "")))
)

Adjust the ranges as required.

Edit: assuming the entries in column A of the allocations sheet are numeric, a version which will work up to the last column A entry in each sheet:

=LET(
    κ,MATCH(7^89,allocations!A:A),
    MAP(
        DROP(TOCOL(A:A,1),1),
        LAMBDA(φ,
            TEXTJOIN(
                ",",,
                IF(allocations!B2:INDEX(allocations!E:E,κ)=φ,
                   allocations!A2:INDEX(allocations!A:A,κ),"")
            )
        )   
    )
)