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

MaddogJason

New Member
Joined
Jan 30, 2021
Messages
20
Office Version
  1. 365
Platform
  1. Windows
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
    Screenshot (16).png
    228.9 KB · Views: 7
Last edited by a moderator:

Some videos you may like

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
Joined
Jun 12, 2014
Messages
55,496
Office Version
  1. 365
Platform
  1. Windows
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.
 

MaddogJason

New Member
Joined
Jan 30, 2021
Messages
20
Office Version
  1. 365
Platform
  1. Windows
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
 

MaddogJason

New Member
Joined
Jan 30, 2021
Messages
20
Office Version
  1. 365
Platform
  1. Windows
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:J15;)1)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,496
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

MaddogJason

New Member
Joined
Jan 30, 2021
Messages
20
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jun 12, 2014
Messages
55,496
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
 

MaddogJason

New Member
Joined
Jan 30, 2021
Messages
20
Office Version
  1. 365
Platform
  1. Windows
Found the little bugger! It was hiding in the countif formula in column H.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,496
Office Version
  1. 365
Platform
  1. Windows
Glad you sorted it & thanks for the feedback.
 

MaddogJason

New Member
Joined
Jan 30, 2021
Messages
20
Office Version
  1. 365
Platform
  1. Windows
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?
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top