1 Header Row with 14 Columns
Column 1 is [places] with repeated data in rows
Column 2 is [Grade] with repeated data in rows
Column 3 to 14 are months from [Jan] to [Dec]
Serial numbers from [C2] to [N5] are unique
Serial numbers are repeated for each [places]
Condition 1 = limit search rows to only Japan in [places]
Condition 2 = search for range [C6] to [N9] containing 60031
Obtain value [Grade 3] with fixed B column [Grade] and row number of 60031
Hope some professional can help. I've tried using nested ifs, vlookup, hlookup, xlookup, and many many formulas...
The challenge is not to use any vba but just excel formulas.
How about
=INDEX(B2:B15,AGGREGATE(15,6,(ROW(B2:B15)-ROW(B2)+1)/(A2:A15=Q2)/(C2:N15=Q3),1))
Thank you for the answer. It works. I've yet to fully understand how aggregate work but your sample has lead me the way.
You're welcome & thanks for the feedback.
