I have a calendar year in Column A and am looking to find a match in column CA. There are multiple matches and within these I am then looking for the match that has "A" in the row next to it. there could be duplicates of this as well and so of those will copy the max value (In column CK). At the moment I have tried some looped vlookup() excel functions but have had no luck. Any help would be appreciated, thanks!

=VLOOKUP(A2&"A", $CA:$CK, 11, FALSE)

CodePudding user response：

Use INDEX/MATCH instead of VLOOKUP().

=INDEX(CK:CK,MATCH(A2&"A",CA:CA&CB:CB,0))

In case of non 365 version of excel, you may need to array entry the formula with CTRL SHIFT ENTER.