Excel search/match

Hakki13

New member
Joined
Feb 14, 2013
Messages
1
Reaction score
0
Points
0
Hi,

Small problem: =LARGE(IF(OR($C:$C=$D3;$J:$J;$D3);$A:$A;"");C16)

Can't get the formula above to work, can anyone see my mistake? Trying to return largest value from A:A, if cell D3 matches C:C or J:J.
A:A is column 1,2,3,4,5,6
C:C is A,B,D,B,D,A
J:J is B,D,A,D,A,B

So if I have B in cell D3 I would like formula return 6,4,2,1 (these are ofcourse in different cells)
 

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
Excel 2016
Try:

=LARGE(IF(($C:$C=$D3)+($J:$J=$D3);$A:$A;"");C16)

you need to confirm this with CTRL+SHIFT+ENTER not just ENTER.

Note: With these types of formulas (array formulas) it is best to avoid whole column references and only reference the max number of rows that you need to. This reduces inefficiency and slowness.

Also, note that C16 (your k factor for LARGE function) is within your lookup column of C:C.... is that correct?
 
Top