Max Lookup In Array

Anonymous

I'm working on something similar to table below:

	FMR1	FMR2	FMR3	FMR4	FMR5	FMR6
5-Jan-13	XXX	2	3	5	6	8
26-Jan-13	1	2		XXX	6	8
25-Feb-13	XXX	2	3	4	7	8

Somehow I'd like to write a vlookup style of formula (might be a maxlookup based on array) that would return the Maximum Value of XXX regardless of what column or ROW the data is in. So for this particular example it would return me:

XXX = February 25, 2013

Any suggestions? I believe it would be some sort of minlookup with an array but I am not that experienced with those level of functions.

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Anonymous

You could try this array-entered****** formula (obviously change the ranges to match your actual data)...

=INDEX(A:A,MAX(IF(B2:G4="XXX",ROW(B2:G4))))

******Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself

0 comments

0 additional answers