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

MaddogJason

New Member
Joined
Jan 30, 2021
Messages
24
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: 191
Last edited by a moderator:

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
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.
 
Upvote 0
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
 
Upvote 0
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)
 
Upvote 0
Ok, thanks for that, how about
Excel Formula:
=INDEX(SORT(FILTER(H4:J15,COUNTIF(A4:A6,H4:H15)=0),3),1,1)
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
Found the little bugger! It was hiding in the countif formula in column H.
 
Upvote 0
Glad you sorted it & thanks for the feedback.
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,483
Messages
6,113,919
Members
448,533
Latest member
thietbibeboiwasaco

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