Search Rows / Match / Return Multiple Values

[tbush33]

Hello, I have been trying to figure out a way to look at a very large data set (Columns A:QZ) and search each row for a value, then if found, return the unique id in Column A. The issue I am running into, is that a single value can have multiple matches throughout the sheet. Here is the example:

In Sheet1, I have a list of 26 unique values.

In Sheet2, I have those same values spread across the worksheet:

I am looking for a formula to put in Sheet1 that will look for the values in that list in Sheet2. When found, I would like it to list out the Unique ID (Column A) for each row that the value is found. I was hoping that the end result would look like this:

Is this something that can be done with a formula?

Thank you!
Travis

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[tbush33]

Thank you, Fluff. I have updated my info. I am using 365 on Windows.

 

[Fluff]

Thanks for that.
How about

=TOROW(IF(Sheet2!$B$2:$U$100=A2,Sheet2!$A$2:$A$100,x),2)

 

[tbush33]

Fluff...if you were next to me, I would give you a solid high five. You will have to settle with an emoji . This is exactly what I needed! Thank you very much for your time.

 

[Fluff]

You're welcome & thanks for the feedback.