# Use Index to Return Unique Values Based on Criteria

#### jwhite0720

##### New Member
I've searched the boards and can't quite seem to find exactly what I'm looking for, however, I apologize if I over looked something and the answer is already out there.

I'm in need of a formula that will reference a data sheet that will have over 100,000 lines of payroll data to return each unique name based on a department number. I will be adding new data every month, so it needs to be dynamic to return new values if there is new data that meets the criteria.

A stripped down version of the data sheet would look like this:

 ColumnD ColumnJ Employee Name Dept# ANDERSON 000170 ANDERSON 000170 ANDERSON 000170 ANDERSON 000170 BECKMANN 000121 BECKMANN 000121 BECKMANN 000121 DAY 000170 DAY 000170 DAY 000170 DAY 000170 GOTTULA 000129 GOTTULA 000129 GOTTULA 000129 GOTTULA 000129 HULL 000170 HULL 000170 HULL 000170 HULL 000170 NGUYEN 000125 NGUYEN 000125 NGUYEN 000125 NGUYEN 000125 HUBER 000155 MATTHIS 000155 GOROMBOL 000122

<tbody>
</tbody>

Assuming 000170 is my criteria, I need to return on a separate sheet each unique name that occurs with the department 000170. I will have a sheet for every department, so a consistent formula that references the same cell containing the department ID will be necessary.

i.e. I want the formula to return the following when dragged down:

Anderson
Day
Hull

I tried the following formula, however it is not returning UNIQUE values, instead it returns every instance that matches the criteria as I drag the formula down.

=IFERROR(INDEX('Payroll Data'!\$D\$2:\$D\$999999,SMALL(IF('Payroll Data'!\$J\$2:\$J\$999999=\$E\$3,ROW('Payroll Data'!\$J\$2:\$J\$999999)-1),ROW(1:1)),0),"")

i.e.
Anderson
Anderson
Anderson
Anderson
Day
Day
Day
Day
Hull
etc.

Last edited:

### Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
for some reason I struggle with these unique formulas but maybe something like this.

though its hard to tell if the Dept. are actual numbers or just formatted to display the leading zeros.

If they are text then you would need to format the value in D1 as text something like....'000170

Excel 2010
ABCDEFGHIJ
1170
2
3UniqueEmployee NameDept#
4ANDERSONANDERSON170
5DAYANDERSON170
6HULLANDERSON170
7ANDERSON170
8BECKMANN121
9BECKMANN121
10BECKMANN121
11DAY170
12DAY170
13DAY170
14DAY170
15GOTTULA129
16GOTTULA129
17GOTTULA129
18GOTTULA129
19HULL170
20HULL170
21HULL170
22HULL170
23NGUYEN125
24NGUYEN125
25NGUYEN125
26NGUYEN125
27HUBER155
28MATTHIS155
29GOROMBOL122

<tbody>
</tbody>
Sheet1

Array Formulas
CellFormula
B4{=IFERROR(INDEX(\$D\$4:\$D\$29,SMALL(IF(FREQUENCY(IF(\$J\$4:\$J\$29=\$D\$1,MATCH(\$D\$4:\$D\$29,\$D\$4:\$D\$29,0)),ROW(\$D\$4:\$D\$29)-ROW(\$D\$4)+1),ROW(\$D\$4:\$D\$29)-ROW(\$D\$4)+1),ROWS(B\$4:B4))),"")}

<tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Awesome! Thank you so much, it sure does take a while to process, but it does work!

Replies
9
Views
1K
Replies
2
Views
452
Replies
3
Views
236
Replies
1
Views
439
Replies
45
Views
2K

1,196,446
Messages
6,015,303
Members
441,887
Latest member
acquamarine

### 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