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

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

#### Fluff

##### MrExcel MVP, Moderator
Hi & welcome to MrExcel.
As you have a number of people with 11 in col J, which of them should be returned?
Also can you please post some sample data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

##### New Member
Hi & welcome to MrExcel.
As you have a number of people with 11 in col J, which of them should be returned?
Also can you please post some sample data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.
CocoaBreak2.0.xlsm
ABCDEFGHIJKLM
130-JanKim
2W5
3AbsentWeekStart DateNameNameNameTotalIndex/100TWeeks36
4SamW530-JanSam21217Sam1
5Kim425-JanSamKimKim21118Kim1
6Henry318-JanDanielKimDaniel21118Daniel1
7211-JanDanielWillWill32313Will1
8HolidaysWillPennyHenry21118Henry1
95114-DecHenryWillRon21513Ron1
10507-DecRonHenryLukas21118Lukas1
114930-NovLukasRonPer21118Per1
124823-NovPerLukasBull21316Bull1
134716-NovBullPerRick21118Rick1
14469-NovRickBullThomas2673Thomas0
15452-NovThomasRickPenny2673Penny0
16PennyThomas2525
17Sam
CocoaBreak (4)
Cell Formulas
RangeFormula
M1M1=INDEX(H4:J15,MATCH(MIN(J4:J15),J4:J15,),1)
A1A1=TODAY()
A2A2="W"&WEEKNUM(A1,2)
I4:I15I4=COUNTIF(E:F,H4)
J4:J15J4=(I4/K4)*100
K4:K15K4=SUM(M4:BG4)
H16H16=COUNTA(E4:F39)
I16I16=SUM(I4:I15)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
I4:I15Cell Valuebelow averagetextNO
I4:I15Cell Valuetop 10% valuestextNO
J4:J15Cellcontains an errortextNO
J4:J15Cell Valuetop 20% valuestextNO
J4:J15Cell Valuetop 20% bottom valuestextNO

##### New Member
Cheers for your reply. At the moment it returns the top one from COL J. Which is fine if there are multiple cells with the same value any will do.

What is import is that id does not pick the names which are put into Range A4:A10.

Perhaps somehow filter away the names in Range A4:A10 and then inserting the now existing function =INDEX(H4:J15;MATCH(MIN(J4:J15);J4:J151)

#### Fluff

##### MrExcel MVP, Moderator

Ok, thanks for that, how about
Excel Formula:
``=INDEX(SORT(FILTER(H4:J15,COUNTIF(A4:A6,H4:H15)=0),3),1,1)``

##### New Member
Ok, thanks for that, how about
Excel Formula:
``=INDEX(SORT(FILTER(H4:J15,COUNTIF(A4:A6,H4:H15)=0),3),1,1)``
Works perfectly! THANK YOU! Not really fair that you did it so fast...

There is one more issue. When I insert both function (my original and yours) into cell E2 it says that it cannot calculate the formula due to circular reference in an open workbook.

I cannot find the issue causing it?

#### Fluff

##### MrExcel MVP, Moderator

Neither formula should give a circular reference unless
a) You have changed the ranges the formula looks at.
b) One of the referenced cells looks at E2

##### New Member
Found the little bugger! It was hiding in the countif formula in column H.

#### Fluff

##### MrExcel MVP, Moderator
Glad you sorted it & thanks for the feedback.

##### New Member
Ok, thanks for that, how about
Excel Formula:
``=INDEX(SORT(FILTER(H4:J15,COUNTIF(A4:A6,H4:H15)=0),3),1,1)``
Works like a charm but I can't exactly wrap my head around how the function works. Can you explain it?

Replies
3
Views
67
Replies
2
Views
82
Replies
0
Views
49
Replies
2
Views
178
Replies
8
Views
170

1,127,481
Messages
5,625,015
Members
416,065
Latest member
meiravmeron

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