Results 1 to 6 of 6

Thread: 'Match' a range, skip a predefined cell

  1. #1
    New Member
    Join Date
    Jun 2012
    Posts
    36
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question 'Match' a range, skip a predefined cell

    I am trying to find where the top 3 values in a range are stored. That part is done, however, if one or more of the top 3 have the same value, I only get the same cell locations and not the top 3.

    Here is how my data looks like:

    A B C D E F G H I J
    0.22
    0.12
    0.19
    0.22
    0.32
    0.27
    0.25
    0.95
    0.95
    0.95

    Formula I am using:

    L M N
    =MATCH(LARGE(A1:J1,1),A1:J1,0) =MATCH(LARGE(A1:J1,2),A1:J1,0) =MATCH(LARGE(A1:J1,3),A1:J1,0)

    Result:

    L M N
    8 8 8

    Result I want:
    L M N
    8 9 10

    Where 8 is column H, 9 is column I and 10 is column J.

    I will appreciate any help!

  2. #2
    Board Regular
    Join Date
    Jan 2015
    Posts
    1,121
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Re: 'Match' a range, skip a predefined cell

    For M,
    I reduce range if countif first value is bigger than 1. I therefore look for large 1 in column 8+1 to 10, so I-J (I use substitute to get from 8 to H) and indirect to have range I1:J1

    Code:
    =IF(COUNTIF(A1:J1,LARGE(A1:J1,1))>1,MATCH(LARGE(A1:J1,1),INDIRECT(SUBSTITUTE(ADDRESS(1,MATCH(LARGE(A1:J1,1),A1:J1,0)+1,4),1,"")&"1:J1"),0)+MATCH(LARGE(A1:J1,1),A1:J1,0),MATCH(LARGE(A1:J1,2),A1:J1,0))
    Would be much easier to solve through VBA for N instead of all the ifs (if count value 1 is 3,if value 1 is 2, if value 1 is 1 and value 2 is >=2, if value1 is 1 and value 2 is 1...

    Last edited by Kamolga; Aug 13th, 2019 at 02:56 PM.

  3. #3
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,203
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: 'Match' a range, skip a predefined cell

    Another option using helper columns

    ABCDEFGHIJKLMNOPQR
    10.220.120.190.220.320.270.250.950.950.950.950.950.958910
    20.220.120.950.220.320.270.250.950.940.950.950.950.953810
    30.220.120.190.950.320.270.250.930.940.950.950.950.944109

    Norfolk



    Worksheet Formulas
    CellFormula
    L1=LARGE($A1:$J1,COLUMN(A1))
    P1=AGGREGATE(15,6,(COLUMN($A1:$J1)-COLUMN($A1)+1)/($A1:$J1=L1),COUNTIF($L1:L1,L1))



    Drag both formulae to the right & down
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  4. #4
    New Member
    Join Date
    Jun 2012
    Posts
    36
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: 'Match' a range, skip a predefined cell

    Quote Originally Posted by Kamolga View Post
    Would be much easier to solve through VBA for N instead of all the ifs (if count value 1 is 3,if value 1 is 2, if value 1 is 1 and value 2 is >=2, if value1 is 1 and value 2 is 1...
    @Kamolga Thank you for the help. Yes, I ended up doing a for loop in VBA.

    Quote Originally Posted by Fluff View Post
    Drag both formulae to the right & down
    Thank you @Fluff, I ended up using VBA, but will keep this as reference for myself.

  5. #5
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,203
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: 'Match' a range, skip a predefined cell

    You're welcome & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  6. #6
    Board Regular
    Join Date
    Jan 2015
    Posts
    1,121
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Re: 'Match' a range, skip a predefined cell

    In VBA, once you have the values, instead of looping 3 times through the range (in this example it does not mater because it is small but maybe you will increase it), you can use Range.findnext to get the second position.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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