# Retrieve One Instance

#### mlo356

##### Board Regular
Hi All,
I have a formula that gives me a unique list of rows by returning its reference #. The formula I am using (Thanks to this amazing forum) is:

In cell E4 insert

Code:
``{=INDEX(C:C,SMALL(IF(\$A\$4:\$B\$20=400,ROW(\$A\$4:\$B\$20)),ROW(A1)))}``

then drag down for the list in the table below.

Notice that I am looking at 2 columns (A and B) and the reference # is returned if either is “400”. I ran into an unexpected problem. For one row, both columns A and B have 400 so instead of a unique list, I now have a delicate reference # in my formula results (Ref # 88888).

Is there a way to modify the formula so that if the same row shows up more than once in the formula, it only keeps one instance therefore making my list unique?

 A​ B​ C​ D​ E​ 1​ 2​ 3​ Data 1​ Data 2​ Reference​ Formula Results​ 4​ 100​ 200​ 11111​ 33333​ 5​ 200​ 100​ 22222​ 44444​ 6​ 300​ 400​ 33333​ 88888​ 7​ 400​ 300​ 44444​ 88888​ 8​ 100​ 500​ 55555​ 12345​ 9​ 200​ 600​ 66666​ 10​ 300​ 800​ 77777​ 11​ 400​ 400​ 88888​ 12​ 200​ 300​ 99999​ 13​ 400​ 700​ 12345​

<tbody>
</tbody>

### Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

#### amgis

##### New Member
Probably not the best answer, but add some lines to add conditional formatting for duplicates. Then add a line to remove cells with the highlighting. You'd have to google the methods.

#### Weazel

##### Well-known Member
maybe something like...

Excel 2013
ABCDE
1
2
3Data 1Data 2ReferenceFormula Results
41002001111133333
52001002222244444
63004003333388888
74003004444412345
810050055555
920060066666
1030080077777
1140040088888
1220030099999
1340070012345

<tbody>
</tbody>
Sheet2

Array Formulas
CellFormula
E4{=IFERROR(INDEX(\$C\$4:\$C\$13,SMALL(IF(FREQUENCY(IF((\$A\$4:\$A\$13=400)+(\$B\$4:\$B\$13=400),MATCH(\$C\$4:\$C\$13,\$C\$4:\$C\$13,0)),ROW(\$A\$4:\$A\$13)-ROW(\$A\$4)+1),ROW(\$A\$4:\$A\$13)-ROW(\$A\$4)+1),ROWS(\$E\$4:E4))),"")}

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

#### mlo356

##### Board Regular
maybe something like...

Excel 2013
A
B
C
D
E
1
2
3
Data 1
Data 2
Reference
Formula Results
4
100
200
11111
33333
5
200
100
22222
44444
6
300
400
33333
88888
7
400
300
44444
12345
8
100
500
55555
9
200
600
66666
10
300
800
77777
11
400
400
88888
12
200
300
99999
13
400
700
12345

<tbody>
</tbody>
Sheet2

Array Formulas
Cell
Formula
E4
{=IFERROR(INDEX(\$C\$4:\$C\$13,SMALL(IF(FREQUENCY(IF((\$A\$4:\$A\$13=400)+(\$B\$4:\$B\$13=400),MATCH(\$C\$4:\$C\$13,\$C\$4:\$C\$13,0)),ROW(\$A\$4:\$A\$13)-ROW(\$A\$4)+1),ROW(\$A\$4:\$A\$13)-ROW(\$A\$4)+1),ROWS(\$E\$4:E4))),"")}

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

This worked great. Thank You. Not entirely sure how it works but it works. Much Appreciated.

Replies
2
Views
281
Replies
7
Views
260
Replies
7
Views
305
Replies
0
Views
320
Replies
2
Views
693

1,195,659
Messages
6,010,957
Members
441,578
Latest member
brodiej

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