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