Power Query Course in Spanish
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: Match column & row headings to return intersecting cell's va

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Location
    Sydney, Australia
    Posts
    80
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Imagine the following 4x4 table, starting at A1 --

    Hot Warm Cold
    Region1 1 4 7
    Region2 2 5 8
    Region3 3 6 9

    From elsewhere on the same worksheet, I want to use a formula to return the value associated with a specific column and row header. For example, I want to be able to specify "Warm" and "Region3" to return the intersecting cell value of '6'.

    Can anyone recommend a formula to do this?

    I've tried the following array formula, which correctly evaluates the initial IF testing, but the bit that is not working is in telling it to return the value from the particular column and row where it found the match --
    {=IF((B1:D1="Warm")*(A2:A4="Region3"),B2:D4)}


    Interestingly this type of formula works fine if the layout is not 2-dimensional (i.e. X-Y), but is instead laid out across the page in a 3x9 table as --

    Hot Region1 1
    Hot Region2 2
    Hot Region3 3
    Warm Region1 4
    Warm Region2 5
    Warm Region3 6
    Cold Region1 7
    Cold Region2 8
    Cold Region3 9

    ..with the array formula of --
    {=IF((A1:A9="Warm")*(B1:B9="Region3"),C1:C9)}

    Any hints would be greatly appreciated

  2. #2
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Jason,

    Try,

    =INDEX(A1:D4,MATCH("Region 1",A1:A4,0),MATCH("warm",A1:D1,0))

    You can also reference cells containing the table headings rather than specifying them by name.

    HTH,
    Jay

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Posts
    390
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    =VLOOKUP("Region3",A1:D4,3,FALSE)


    [ This Message was edited by: Albert 1 on 2002-03-21 17:16 ]

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    Sydney, Australia
    Posts
    80
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thank you both for the excellent suggestions!

    I'm actually going to use a mix of both of these formulae -- with VLOOKUP doing the work, but using MATCH to determine the column index rather than specifying it with an index number.

    Thanks again!

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    Guderup, Denmark
    Posts
    288
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    You could also use natural language by selecting all cells in your matrix.
    Choose insert / Insert Names / Create and and checkmark Top row and left column.
    Now you can refer to the matrix this way:

    =Region1 Warm
    or =Cold Region2

    Remember the space between the names.

    regards Tommy

  6. #6
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-22 06:35, Tommy Bak wrote:
    You could also use natural language by selecting all cells in your matrix.
    Choose insert / Insert Names / Create and and checkmark Top row and left column.
    Now you can refer to the matrix this way:

    =Region1 Warm
    or =Cold Region2

    Remember the space between the names.

    regards Tommy
    To comment further...

    You don't even need to create names. Just check the "Accept labels in formulas" option on the Calculation tab using the Tools | Options... menu command.

  7. #7
    Board Regular
    Join Date
    Feb 2002
    Location
    Guderup, Denmark
    Posts
    288
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks a lot, Mark.
    I allways wondered what that option actually did.
    regards Tommy

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

    Default

    On 2002-03-23 01:00, Tommy Bak wrote:
    Thanks a lot, Mark.
    I allways wondered what that option actually did.
    regards Tommy
    Handy when you must/want to use the intersection operator, but quite unreliable when used in lieu of ordinary range refs or range names.

    Aladin

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
  •