Finding a value in a multiple column array and returning column header - Excel 2003 - Page 8

Thanks Thanks:  0
Likes Likes:  0
Page 8 of 8 FirstFirst ... 678
Results 71 to 76 of 76

Thread: Finding a value in a multiple column array and returning column header - Excel 2003

  1. #71
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,465
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Finding a value in a multiple column array and returning column header - Excel 2003

     
    Quote Originally Posted by HelderOliveira View Post
    Sorry, it is not complet...
    I haave changed tje COMULMN funtion by the ROW funtion, changed the cells identification and it did not work... This is the data matrix for this question:

    A B C D E F G
    CREA 10 11 21 31 41 60
    ACAB 20 30 40 50 52 53
    VGAM 20S 30S 40S 50S 52S 53S

    Want I whant is giving the 50S, receive the VGAM result...
    Thank you
    HelderOliveira
    Row\Col
    A
    B
    C
    D
    E
    F
    G
    K
    1
    CREA
    10
    11
    50S
    31
    41
    60
    50S
    2
    ACAB
    20
    30
    40
    50
    52
    53
    2
    3
    VGAM 20S 50S 40S 50S 52S 53S CREA
    4
    VGAM
    5
    6


    In K2 control+shift+enter, not just enter:

    =SUM(IF(FREQUENCY(IF($B$1:$G$3=K$1,ROW($A$1:$A$3)-ROW($A$3)+1),ROW($A$1:$A$3)-ROW($A$3)+1),1))

    In K3 control+shift+enter, not just enter, and copy down:

    =IF(ROWS($K$3:K3)>K$2,"",INDEX($A$1:$A$3,SMALL(IF($B$1:$G$3=K$1,ROW($A$1:$A$3)-ROW($A$1)+1),ROWS($K$3:K3))))
    Assuming too much and qualifying too much are two faces of the same problem.

  2. #72
    New Member
    Join Date
    Jan 2018
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Finding a value in a multiple column array and returning column header - Excel 2003

    Hello. I'm trying to do something similar only without the ranges being next to each other. Here's an example
    Row/Col A B C D E F
    1 RangeA RangeB LookingFor FoundIn
    2 aa ff cc RangeA
    3 bb gg ii RangeB
    4 cc hh aa RangeA
    5 dd ii

    Is this possible?

  3. #73
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,465
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Finding a value in a multiple column array and returning column header - Excel 2003

    @ NemoS

    Is there any reason why the following would not work?

    In F2 control+shift+enter, not just enter, and copy down:

    =INDEX($A$1:$C$1,MIN(IF($A$2:$C$5=$E2,COLUMN($A$1:$C$1)-COLUMN($A$1)+1)))
    Assuming too much and qualifying too much are two faces of the same problem.

  4. #74
    New Member
    Join Date
    Jan 2018
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Finding a value in a multiple column array and returning column header - Excel 2003

    Quote Originally Posted by Aladin Akyurek View Post
    @ NemoS Is there any reason why the following would not work? In F2 control+shift+enter, not just enter, and copy down: =INDEX($A$1:$C$1,MIN(IF($A$2:$C$5=$E2,COLUMN($A$1:$C$1)-COLUMN($A$1)+1)))
    It works as long as each range header is on the same row, which they wont always be. For example:
    Row/Col A B C D E F
    1 RangeA RangeB LookingFor FoundIn
    2 aa ff cc RangeA
    3 bb gg ii RangeB
    4 cc hh aa RangeA
    5 dd ii kk RangeC
    6 oo RangeD
    7 RangeC RangeD
    8 jj oo
    9 kk
    10 ll qq
    11 rr
    Then you can imagine ranges E and F will start with headers in row 13 and contain up to 4 strings under each as well. Each range will always be the same size, though some entries may be empty within that range. There will also be other data in-between, like columns B and D, though that data will never contain any strings that would be found.

  5. #75
    New Member
    Join Date
    Jan 2018
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Finding a value in a multiple column array and returning column header - Excel 2003

    Never-mind! I have found a solution! Posting here for anyone else who wants it: =ArrayFormula(if(len(E2:E6), vlookup(E2:E6, split({A2:A5&"_"&A1; C2:C5&"_"&C1; A8:A11&"_"&A7; C8:C11&"_"&C7}, "_"), 2, 0),)) This was done in google sheets so I'm not sure if it'll work the same in MS excel. Usually if it works in one it'll work in both though...

  6. #76
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,465
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Finding a value in a multiple column array and returning column header - Excel 2003

      
    Quote Originally Posted by NemoS View Post
    Never-mind! I have found a solution! Posting here for anyone else who wants it: =ArrayFormula(if(len(E2:E6), vlookup(E2:E6, split({A2:A5&"_"&A1; C2:C5&"_"&C1; A8:A11&"_"&A7; C8:C11&"_"&C7}, "_"), 2, 0),)) This was done in google sheets so I'm not sure if it'll work the same in MS excel. Usually if it works in one it'll work in both though...
    1. Your initial sample and the later sample are quite different qua data representation they involve.

    2. Excel does not include a 'split' function, so the formula has not much to offer to an Excel user.

    3. Try the following if you would need Excel...

    Row\Col
    A
    B
    C
    D
    E
    F
    1
    RangeA RangeB LookingFor FoundIn
    2
    aa ff cc RangeA
    3
    bb gg ii RangeB
    4
    cc hh aa RangeA
    5
    dd ii kk RangeC
    6
    oo RangeD
    7
    RangeC RangeD
    8
    jj oo
    9
    kk
    10
    ll qq
    11
    rr


    In F2 just enter and copy down:

    =LOOKUP(REPT("z",255),CHOOSE({1,2,3,4},IF(MATCH(E2,$A$2:$A$5,0),$A$1),IF(MATCH(E2,$C$2:$C$5,0),$C$1),IF(MATCH(E2,$A$8:$A$11,0),$A$7),IF( MATCH(E2,$C$8:$C$11,0),$C$7)))
    Assuming too much and qualifying too much are two faces of the same problem.

User Tag List

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
  •  

 

 
DMCA.com