# INDEX MATCH, but how to exclude a specific range from the search?

##### New Member
I´ve used the following function to find the lowest value in range J4:J15.

Excel Formula:
``=INDEX(H4:J15;MATCH(MIN(J4:J15);J4:J15,)1)``

It works perfectly. Thank you very much! I am mightily proud of my achievement!

However, now I would like to exclude specific values from being picked up.

In the example below Sam appears in M1 because he has the lowest value in cell J4:J15

I want to exclude Sam from showing up based on that his name is put into the range A4:A10

In the example, the function should exclude both Sam and Kim from being picked.

#### Attachments

• Screenshot (16).png
228.9 KB · Views: 7
Last edited by a moderator:

#### Fluff

##### MrExcel MVP, Moderator
The countif returns an array of 1s & 0s depending on if the name in H4:H15 is in A4:A6
{1;1;0;0;1;0;0;0;0;0;0;0}
this is turned into array of true/false dependin on whether they =0
{FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE}
The Filter function then filters out those rows where false is returned.
leaving you with
{"Daniel",2,11;"Will",3,23;"Ron",2,15;"Lukas",2,11;"Per",2,11;"Bull",2,13;"Rick",2,11;"Thomas",2,67;"Penny",2,67}
this is then sorted ascending based on the 3rd column in the array (col J)
{"Daniel",2,11;"Lukas",2,11;"Per",2,11;"Rick",2,11;"Bull",2,13;"Ron",2,15;"Will",3,23;"Thomas",2,67;"Penny",2,67}
And finally Index returns the 1st row & column from that array.

One way of finding out what a formula is doing si to use the Evaluate Formula button on the formula tab.

### Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

##### New Member
THANK YOU! This is the part where my head melts...

Let's say I would want to add one particular cell which would also be filtered out.

If the value exists in Cell E4 it would also be filtered out.

basically, Any value that shows in A4:A:10 AND in E4 would not show up in search.

#### Fluff

##### MrExcel MVP, Moderator
Excel Formula:
``=INDEX(SORT(FILTER(H4:J15,(COUNTIF(A4:A6,H4:H15)=0)*(H4:H15<>E4)),3),1,1)``

##### New Member
Excel Formula:
``=INDEX(SORT(FILTER(H4:J15,(COUNTIF(A4:A6,H4:H15)=0)*(H4:H15<>E4)),3),1,1)``
PFFFT! Yeah, that is exactly it. I still seem to have a thing or two to learn... I´m very impressed. Thank you!!!!

#### Fluff

##### MrExcel MVP, Moderator
You're welcome & thanks for the feedback

Replies
3
Views
72
Replies
2
Views
87
Replies
0
Views
63
Replies
2
Views
179
Replies
8
Views
172

1,127,808
Messages
5,627,010
Members
416,214
Latest member
boston814

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