I am trying to use an INDEX MATCH to return the optimal choice from a list of items associated to values that change based on various criteria. My current sheet setup and formula is this:
A B C
Item1 Bad 27
Item2 Good 15
Item3 Good 27
Item4 Bad 44
Column A is a named range Item
.
Column B is a named range ItemType
.
Column C is a named range ItemValue
=INDEX(INDIRECT(SUBSTITUTE('Sheet'!B3," ","")),MATCH(MAX(IF(INDIRECT(SUBSTITUTE('Sheet'!B3," ","")&"Type")="Good",INDIRECT(SUBSTITUTE('Sheet'!B3," ","")&"Value"))),INDIRECT(SUBSTITUTE('Sheet'!B3," ","")&"Value"),0))
(entered as an array formula)
In its current iteration, this will return Item1
. I would like it to return Item3
.
In this example, 'Sheet'!B3
contains the category to search (in this example Item
). I use a variety of named ranges based on the data in that cell and suffixes with the use of INDIRECT/SUBSTITUTE. My current formula finds the highest value that also is Good
, but then returns the first value that matches it, regardless of it being Good
or Bad
. I have tried adding an additional IF statement at different points of the formula but the cell would either return FALSE
or a formula syntax error. How can I maintain my ="Good"
IF statement throughout the formula?
Thank you!
sheet
and what you would like the solution to be based on the sampled data you provided. – dimitris_pssheet
is cellB3
, which containsItem
in order to point the indirect substitutes to the correct named ranges. The named ranges are in a separatesource
sheet. As I mentioned, I would like the solution to beItem3
in this case, as this is the highest value in Column C/ItemValue
that is alsoGood
in Column B/ItemType
. Hope this clears things up. – Organicbeing