Index Match in Alphabetical Order with Multiple Criteria

I would like to have an index match function provide a unique list of items in alphabetical order and with multiple criteria. This is an additional criteria related to my previous thread, https://www.excelforum.com/excel-for...-criteria.html.

Column A may include "Y", which would determine if it should be included. To sum this up, two criteria to index match in alphabetical order is based on if column B includes "AI" and Column A includes "Y". Column H would have the resulting solutions. Hope this helps.

Thank you,
Tyler

your dataset appears to be in A-Z order implying you don't need to re-sort in the results but rather return them in the order they are found, correct?

further to the above, there are no duplicates in your sample either -- if that's the case then the ISNUMBER(MATCH)) piece becomes superfluous.

Formula:

Please Login or Register  to view this content.

the above assumes you're using XL2007 per your profile, rather than a later version.

Thanks Bo_Ry, this worked out!

no need to quote prior posts in full, you'll get into trouble with the mods!

below should work -- does not require array entry (c/o additional INDEX -- no more efficient but easier to commit / edit etc!)

Formula:

Please Login or Register  to view this content.

edit: per earlier comment I assumed XL2007 (i.e. no AGGREGATE)