EXCEL LOOK UP FORUMLA

Question

I've two separate excel files, one containing a list of users which are assigned to a specific team, on the other excel I would like to search the first file to validate if the "user id" is contained on the list of users on "user list" excel and if yes then to tell me which user team they are in.

I've tried

=XLOOKUP([@[USER LIST]],'DATA Sheet'!A5:A128,'DATA Sheet'!B5:B128,[@[USER LIST]])

Answer 1

Use AGGREGATE:

=INDEX('DATA Sheet'!$A$1:$C$1,1,AGGREGATE(15,7,COLUMN('DATA Sheet'!$A$2:$C$128)/('DATA Sheet'!$A$2:$C$128=A2),1))

Answer 2

With SUM and INDEX

=INDEX('DATA Sheet'!$A$1:$C$1,,--SUM((A2='DATA Sheet'!$A$2:$C$128)*{1\2\3}))