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

jasonconlon

Board Regular
Joined
Mar 14, 2002
Messages
80
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
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
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
 
Upvote 0
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!
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top