Thanks:  0
Likes:  0

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

1. 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. 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. =VLOOKUP("Region3",A1:D4,3,FALSE)

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

4. 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. 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. 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. Thanks a lot, Mark.
I allways wondered what that option actually did.
regards Tommy

8. 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.

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•