Multi Criteria "Not Equal to" Range - Index Match formula

[Dendrinos2]

I am trying to come up with a Multi Criteria Index Match formula with a "not equal to function" (<>) where the the "<>" is in reference to a range of data not just a single cell. Im stuck on creating the reference to a range of values not just a single value. My thinking is I might need to use a double unary but I cant figure it out.

The table below is an example for formula with my data in cells A1:B6 - I want to replace the "<>"Grapes"" with a reference to a range - for example my range might reference the 2 cells below the formula (Grapes, Oranges)

The answer would then be "Peaches" - also I dont want to use an array constant as my "<>" reference range will be changing.

Any help would be greatly appreciated


[TABLE="width: 500"]
<tbody>[TR]
[TD]Foods[/TD]
[TD]# of Items[/TD]
[/TR]
[TR]
[TD]Apples[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Bananas[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Grapes[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Oranges[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Peaches[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]^=INDEX($A$2:$A$6,MATCH(1,INDEX(($B$2:$B$6=4)*($A$2:$A$6<>"Grapes"),0),0))[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Grapes[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Oranges[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Eric W]

Try:

=INDEX($A$2:$A$6,MATCH(1,INDEX(($B$2:$B$6=4)*(ISERROR(MATCH($A$2:$A$6,$A$9:$A$10,0))),0),0))

 

[Dendrinos2]

Wow. I was on the struggle bus with this for hours. Thanks so much!

 

[Eric W]

You're welcome!