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

[jasonconlon]

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

[Jay Petrulis]

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

[Albert 1]

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


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

[jasonconlon]

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!

[Tommy Bak]

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

[Mark W.]

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.

[Tommy Bak]

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

[Aladin Akyurek]

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