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

Foods	# of Items
Apples	2
Bananas	3
Grapes	4
Oranges	4
Peaches	4
^=INDEX($A$2:$A$6,MATCH(1,INDEX(($B$2:$B$6=4)*($A$2:$A$6<>"Grapes"),0),0))
Grapes
Oranges

<tbody>
</tbody>

 

[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!