Finding & Highlighting Duplicate Values across multiple sheets- HELP!

[Lexi_2206]

Ok,
I have a sheet of about 500 rows of data. The first column contains course titles going all the way down to row 498. Each column title after that is the seat # for that particular course based on the size of the class. The names for each course and the corresponding students that have signed up for that course are listed in rows.Depending on the classroom size, each course has a different number of available seats which means there are blank cells in the table range of my data. What I'm trying to do is cross reference a specific student name against all of the columns that contain names of students who have signed up for a course and then highlight the values where there are matches. What would be even more ideal instead of going through the entire worksheet to look for the highlights is having it return all of the course titles (located in the first column down to row 498) for my reference value. I watched multiple videos on IF, COUNTIF and MATCH functions but I just can't get anything to work. I'm familiar with VLOOKUP but I need something that can return multiple values instead of one. I have spent 3 days trying to tackle this, please help!

 

[portews]

It looks like the fastest, easiest way is to use Countif and an autofilter. In a new column, put the following formula into row 2:

=COUNTIF($B2:$AE2,$AG$1)

for column B to the max number of seats (AE represents 30 seats), and $AG$1 is the where you put the student name to search. Drag the formula all the way down to 498. This will put a number in that column representing how many times in that row it finds that name. (I assume it shouldn't be more than one per row, but it's also a good error check.) Do an autofilter on that column for anything greater than zero. It will hide all the rows that don't have the name in it, leaving you with a short list of classes.

Will that do what you want?

 

[Lexi_2206]

Thank you. It works but I was looking for something that can look for multiple names at once. I used the formula above but instead of referencing one cell (AG1) I referenced a range of cells and the formula returned 0's in every row. Is there a different formula that will use a ranged criteria across multiple column/rows and return a value in column 1 when it's found? Similar to Vlookup?