1. Forum
  2. Usenet
  3. MICROSOFT.PUBLIC.EXCEL

  • Index Sort

    From David Cuthill@21:1/5 to All on Thu Sep 10 23:00:07 2020
    I think this is an index match question but cant get my head around how to do what I want.

    I have 2 columns of data.

    Column 1 is a number of angles and column 2 is the depth at which these angle happens. The angles may repeat several times but the depths are each unique values.

    I want to be able to derive a formula that would allow me to select an angle in a separate cell and then in an adjacent column present all the depths at which that angle happens.

    if column A was the angles and B the depths. If I was to select 42.56 in cell C2 I would like D2, D3 ..... to show that depths at which that angle takes place.

    I have tried using =INDEX($B2:$B$10,MATCH($C$2,$A2:$A$10,0)) and then copy it down column D but it only finds the first instance of the angle until the match updates.

    I would appreciate any ideas on how to reduce the top of the match range once a match has been made to that the formula is forward looking.

    angles depth
    0.00 10
    42.56 20
    102.56 30
    180.00 40
    102.56 50
    42.56 60
    60.00 70
    42.56 80
    102.56 90

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Claus Busch@21:1/5 to All on Fri Sep 11 09:27:50 2020
    Hi David,

    Am Thu, 10 Sep 2020 23:00:07 -0700 (PDT) schrieb David Cuthill:

    I want to be able to derive a formula that would allow me to select an angle in a separate cell and then in an adjacent column present all the depths at which that angle happens.

    if column A was the angles and B the depths. If I was to select 42.56 in cell C2 I would like D2, D3 ..... to show that depths at which that angle takes place.

    I have tried using =INDEX($B2:$B$10,MATCH($C$2,$A2:$A$10,0)) and then copy it down column D but it only finds the first instance of the angle until the match updates.

    I would appreciate any ideas on how to reduce the top of the match range once a match has been made to that the formula is forward looking.

    angles depth
    0.00 10
    42.56 20
    102.56 30
    180.00 40
    102.56 50
    42.56 60
    60.00 70
    42.56 80
    102.56 90

    have a look:
    https://1drv.ms/x/s!AqMiGBK2qniTge8Pn4txmmyNg76-0Q?e=n5QZjM


    Regards
    Claus B.
    --
    Windows10
    Office 2016

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From David Cuthill@21:1/5 to claus...@t-online.de on Fri Sep 11 14:16:48 2020
    On Friday, 11 September 2020 at 01:27:55 UTC-6, claus...@t-online.de wrote:
    Hi David,
    Am Thu, 10 Sep 2020 23:00:07 -0700 (PDT) schrieb David Cuthill:

    I want to be able to derive a formula that would allow me to select an angle in a separate cell and then in an adjacent column present all the depths at which that angle happens.

    if column A was the angles and B the depths. If I was to select 42.56 in cell C2 I would like D2, D3 ..... to show that depths at which that angle takes place.

    I have tried using =INDEX($B2:$B$10,MATCH($C$2,$A2:$A$10,0)) and then copy it down column D but it only finds the first instance of the angle until the match updates.

    I would appreciate any ideas on how to reduce the top of the match range once a match has been made to that the formula is forward looking.

    angles depth
    0.00 10
    42.56 20
    102.56 30
    180.00 40
    102.56 50
    42.56 60
    60.00 70
    42.56 80
    102.56 90
    have a look:
    https://1drv.ms/x/s!AqMiGBK2qniTge8Pn4txmmyNg76-0Q?e=n5QZjM


    Regards
    Claus B.
    --
    Windows10
    Office 2016


    Thanks Claus your solution works perfectly.

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)