CODEWITHSUNDEEP
excelvbavlookup
kca062
kca062

Reputation: 63

Multiple VLOOKUPs and a max value

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)

Data

Upvotes: 0

Views: 309

Answers (1)

Harun24hr
Harun24hr

Reputation: 36750

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.

enter image description here

Upvotes: 1

Related Questions