# Multiple unique results from search and formatting of results

#### Falcoty

##### New Member
My question is in two parts.

First, how to fix the formula I have, or create a new one, to get only the unique values when I perform my search.

Second, how to also set a conditional format so that active models will be highlighted one color and inactive models will be highlighted another.

This is how the document currently appears.

I would like the end result to look more like this. There is only one return value for each Model, and they are highlighted to indicate activity.

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

#### Falcoty

##### New Member
Of course the pictures don't work. I figured out a workaround for the formatting, I'm still trying to figure out how to get unique values.

 Model Part# Search 30001 Model2 30001 Model2 30001 Result Model2 Model2 30001 Model2 Model2 30001 Model2 Model3 30001 Model2 Model3 30001 Model3 Model2 30002 Model3 Model2 30002 #NUM! Model3 30002 #NUM! Model3 30002 #NUM! Model4 30002 Model4 30002 Model1 65001 Model1 65001 Model1 65001 Model1 65001 Model2 77016 Model2 77016 Model3 77016 Model3 77016 Model4 77016

<colgroup><col style="mso-width-source:userset;mso-width-alt:3766;width:77pt" width="103"> <col style="mso-width-source:userset;mso-width-alt:3328;width:68pt" width="91"> <col style="width:48pt" span="2" width="64"> </colgroup><tbody>
</tbody>

{INDEX(\$A:\$A,SMALL(IF(\$D\$1=\$B:\$B, ROW(\$A\$2:\$A\$22)-ROW(\$D\$2)+1), ROW(1:1)))}

The formula I have is almost idea, except that it returns every instance of when the given search value is used, instead of the first one of each unique model.

Replies
15
Views
833
Replies
4
Views
866
Replies
3
Views
625
Replies
10
Views
3K
Replies
5
Views
1K

1,195,683
Messages
6,011,138
Members
441,587
Latest member
kbsgiri09

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