Attend Excelapalooza
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 9 123 ... LastLast
Results 1 to 10 of 81

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

  1. #1
    New Member
    Join Date
    Dec 2012
    Location
    Seattle
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Hi guys first post here.

    I am attempting to lookup a cell value in a 3 column by 20 row array. Depending on which column the value is found, I would like to return that column's header value.

    I think I am making this overly complicated for myself. Basically I need a statement that will perform an Hlookup on three columns but I'm not sure exactly how to do this.

    Any help would be appreciated.

    Thanks!

  2. #2
    Board Regular T. Valko's Avatar
    Join Date
    May 2009
    Location
    Pittsburgh
    Posts
    16,623
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

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

    Quote Originally Posted by NairbNam View Post
    Hi guys first post here.

    I am attempting to lookup a cell value in a 3 column by 20 row array. Depending on which column the value is found, I would like to return that column's header value.

    I think I am making this overly complicated for myself. Basically I need a statement that will perform an Hlookup on three columns but I'm not sure exactly how to do this.

    Any help would be appreciated.

    Thanks!
    Try this...

    Sheet1

     ABCDEF
    1Header1Header2Header3_LookupLocation
    2221065_94Header2
    3441559___
    4489431___
    5332986___

    This array formula** entered in F2:

    =INDEX(A1:C1,MAX(IF(A2:C5=E2,COLUMN(A2:C5)-COLUMN(A1)+1)))

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.
    .
    Biff
    Microsoft MVP - Excel

    Don't be afraid to use volatile functions or array formulas
    Tell us what version of Excel you're using
    KISS - Keep It Simple Stupid

  3. #3
    New Member
    Join Date
    Dec 2012
    Location
    Seattle
    Posts
    6
    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

    Perfect! This is exactly what I was looking for, thank you.

  4. #4
    Board Regular T. Valko's Avatar
    Join Date
    May 2009
    Location
    Pittsburgh
    Posts
    16,623
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

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

    Quote Originally Posted by NairbNam View Post
    Perfect! This is exactly what I was looking for, thank you.
    You're welcome. Thanks for the feedback!
    .
    Biff
    Microsoft MVP - Excel

    Don't be afraid to use volatile functions or array formulas
    Tell us what version of Excel you're using
    KISS - Keep It Simple Stupid

  5. #5
    Board Regular
    Join Date
    Aug 2009
    Posts
    1,335
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

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

    i want to do something similar but only in one row and instead of a max number i need to match a string. You couldn't help me out too, could you please?

  6. #6
    New Member
    Join Date
    Aug 2013
    Posts
    21
    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 T. Valko View Post
    Try this...

    Sheet1

    A B C D E F
    1 Header1 Header2 Header3 _ Lookup Location
    2 22 10 65 _ 94 Header2
    3 44 15 59 _ _ _
    4 48 94 31 _ _ _
    5 33 29 86 _ _ _

    This array formula** entered in F2:

    =INDEX(A1:C1,MAX(IF(A2:C5=E2,COLUMN(A2:C5)-COLUMN(A1)+1)))

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.

    How about if my Lookup is "Header1" and the data i want to get is in cell A3 = 44 ... what formula should be?

  7. #7
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,382
    Post Thanks / Like
    Mentioned
    29 Post(s)
    Tagged
    6 Thread(s)

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

    Quote Originally Posted by CherGian View Post
    How about if my Lookup is "Header1" and the data i want to get is in cell A3 = 44 ... what formula should be?
    Unclear what you mean. Would you post a small sample along with the expected actual result?
    Assuming too much and qualifying too much are two faces of the same problem.

  8. #8
    New Member
    Join Date
    Aug 2013
    Posts
    21
    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
    Unclear what you mean. Would you post a small sample along with the expected actual result?
    Hi Aladin,

    Below is the result i want..
    A B C D E F
    1 Header1 Header2 Header3 _ Header1 <<--Lookup Value
    2 22 10 65 _ 22 <<-- Result
    3 44 15 59 _ 44 <<-- Result
    4 48 94 31 _ 48 <<-- Result
    5 33 29 86 _ 33 <<-- Result
    As every time i changed the Lookup Value (cell E1), the result will also change as to specified look up column.
    Hope this will clarify of my query.

    Thanks a lot.

  9. #9
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    37,423
    Post Thanks / Like
    Mentioned
    54 Post(s)
    Tagged
    9 Thread(s)

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

    You could do it with the single formula in E2, copied down. However, to me it makes sense to use the method shown in columns G:H as the formulas are considerably simpler and the same calculation to determine the relevant column does not need to be continually repeated.

    Extract Column

     ABCDEFGH
    1Header1Header2Header3 Header2 Header22
    2221065 10 10 
    3441559 15 15 
    4489431 94 94 
    5332986 29 29 
    6        
    7554433 44 44 
    8        

    Spreadsheet Formulas
    CellFormula
    H1=MATCH(G1,A1:C1,0)
    E2=IF(INDEX(A$2:C$10,ROWS(E$2:E2),MATCH(E$1,A$1:C$1,0))="","",INDEX(A$2:C$10,ROWS(E$2:E2),MATCH(E$1,A$1:C$1,0)))
    G2=IF(INDEX(A2:C2,H$1)="","",INDEX(A2:C2,H$1))


    Excel tables to the web >> Excel Jeanie HTML 4
    Hope this helps, good luck.
    Peter
    Excel 365 + Excel 2010, 2007 - Windows 10, 7
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  10. #10
    New Member
    Join Date
    Aug 2013
    Posts
    21
    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 Peter_SSs View Post
    You could do it with the single formula in E2, copied down. However, to me it makes sense to use the method shown in columns G:H as the formulas are considerably simpler and the same calculation to determine the relevant column does not need to be continually repeated.

    Extract Column

    A B C D E F G H
    1 Header1 Header2 Header3 Header2 Header2 2
    2 22 10 65 10 10
    3 44 15 59 15 15
    4 48 94 31 94 94
    5 33 29 86 29 29
    6
    7 55 44 33 44 44
    8

    Spreadsheet Formulas
    Cell Formula
    H1 =MATCH(G1,A1:C1,0)
    E2 =IF(INDEX(A$2:C$10,ROWS(E$2:E2),MATCH(E$1,A$1:C$1,0))="","",INDEX(A$2:C$10,ROWS(E$2:E2),MATCH(E$1,A$1:C$1,0)))
    G2 =IF(INDEX(A2:C2,H$1)="","",INDEX(A2:C2,H$1))


    Excel tables to the web >> Excel Jeanie HTML 4

    Thank you so much Peter! It Works!

Some videos you may like

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
  •