Dendrinos2
New Member
- Joined
- Nov 2, 2011
- Messages
- 30
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
<tbody>
</tbody>
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>
Last edited: