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)
 
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?
 
Back
Top