Results 1 to 2 of 2

Thread: Excel search/match

  1. #1

    Excel search/match



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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)

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,437
    Articles
    0
    Excel Version
    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?


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •