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

[MaddogJason]

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

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

 

[Fluff]

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.

 

[MaddogJason]

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]

How about

=INDEX(SORT(FILTER(H4:J15,(COUNTIF(A4:A6,H4:H15)=0)*(H4:H15<>E4)),3),1,1)

 

[MaddogJason]

How about

=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]

You're welcome & thanks for the feedback