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:

Fluff

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

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

MaddogJason

New Member
Joined
Jan 30, 2021
Messages
20
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jun 12, 2014
Messages
55,498
Office Version
  1. 365
Platform
  1. Windows
How about
Excel Formula:
=INDEX(SORT(FILTER(H4:J15,(COUNTIF(A4:A6,H4:H15)=0)*(H4:H15<>E4)),3),1,1)
 
Solution

MaddogJason

New Member
Joined
Jan 30, 2021
Messages
20
Office Version
  1. 365
Platform
  1. Windows
How about
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
Joined
Jun 12, 2014
Messages
55,498
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,127,484
Messages
5,625,034
Members
416,064
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