Finding a value in a multiple column array and returning column header - Excel 2003 - Page 8
Finding a value in a multiple column array and returning column header - Excel 2003
Thanks Thanks:  0
Likes Likes:  0
Page 8 of 9 FirstFirst ... 6789 LastLast
Results 71 to 80 of 81

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
    82,357
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    5 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
    82,357
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    5 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
    82,357
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    5 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.

  7. #77
    New Member
    Join Date
    Feb 2018
    Posts
    1
    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

    I'm trying to do something similar though my columns each have a different number of items in them.

    Column "type of" is my expected result. I'm looking at the items in the column "type" and then wanting to find the header (Single Family, Multi Family, or Non-Res") Note - The number column isn't something that needs to be matched in this. Thank you!

    Number Type Type of Single Family Multi Family Non-Res
    1 House Single Family House Condo Park
    2 Condo Multi Family Apartment Store
    3 Apartment Multi Family Warehouse
    4 Park Non-Res Restaurant

  8. #78
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    82,357
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    5 Thread(s)

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

    @ jenny1013

    Row\Col
    A
    B
    C
    D
    E
    F
    G
    1
    Number Type Type of Single Family Multi Family Non-Res
    2
    1
    House Single Family House Condo Park
    3
    2
    Condo Multi Family Apartment Store
    4
    3
    Apartment Multi Family Warehouse
    5
    4
    Park Non-Res Restaurant

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

    =IF(COUNTIFS($E$2:$G$5,$B2),INDEX($E$1:$G$1,MIN(IF($E$2:$G$5=$B2,COLUMN($E$1:$G$1)-COLUMN($E$1)+1))),"not found")
    Assuming too much and qualifying too much are two faces of the same problem.

  9. #79
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    82,357
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    5 Thread(s)

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

    Please replace COUNTIFS with COUNTIF for your version of Excel.
    Assuming too much and qualifying too much are two faces of the same problem.

  10. #80
    New Member
    Join Date
    Feb 2018
    Posts
    1
    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

      
    Hi

    This is my first post so a little nervous.

    I have a situation very similar to the one shown here but occasionally there are multiple repeated values.

    I thought i'd got around this by using LARGE instead of MAX in the equation. I have the value of 'k' outside the equation in a convenient cell where I can change it at will.

    I'd better mention that I have the equation calculating the ROW header as well as the COLUMN header...

    Using the example above I have

    The columns B,C and D instead of Header_1, Header_2 etc.
    And I am using the line numbers 40 to 43. I am using cell E39 as my 'k' value. Hence

    {=INDEX(B39:D39,LARGE(IF(B40:D43=E40,COLUMN(B40:D43)-COLUMN(B39)+1),E39))} For the row number and

    {=INDEX(A40:A43,LARGE(IF(B40:D43=E40,ROW(B40:D43)-ROW(A40)+1),E39))} for the column letter.

    So here's the thing... Sometimes it works and sometimes it doesn't.

    If I use the original formula (i.e. MAX) when there are ... say three values of 48; the solution will give the position of the third value.
    and using the above equation I get the same result when 'k' is set to 1. No problem so far.

    However, if one of the duplicate values happens to be on the same row or column the COLUMN value might be right but sometimes the ROW is not. By changing E39 to 2 or 3 (i.e. the 'k' value) it will only give the correct cell (via the two equations above) if all three are on different rows and different columns.

    All the above was used to see if it would work on my real database. I wasn't convinced... So I went ahead and did it anyway.
    Sometimes this is what is happening.

    If the real solutions are H4, M3 and V4 (to all intents and purposes these are cell addresses)
    The answers I am getting are... H3, M4 and V4. (The first pair column H and row 4 are both using a 'k' value of 3 which is correct; yet the result is incorrect. And this is so annoying!

    Has anyone any ideas about the equations? Is there something I am missing?

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