# Finding & Highlighting Duplicate Values across multiple sheets- HELP!

#### Lexi_2206

##### New Member
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!

### Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

#### portews

##### Active Member
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:

Code:
``=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

##### New Member
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?

Replies
5
Views
401
Replies
7
Views
351
Replies
11
Views
475
Replies
3
Views
352
Replies
13
Views
297

1,190,811
Messages
5,983,046
Members
439,816
Latest member
aggelos

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back