How to get the value in a matrix?

[Jeppe]

Below you see a matrix.

I want to give a function i.e. the letter b and the digist 2 and the
recieve the value 6,9...

***a* * * b* * *c*
*1* 0,5 0,2 0,5
*2 * 7,1 6,9 7,8

Which function can I use?

Thanks..

 

[JulieD]

Hi Jeppe

one way

assuming a, b, c are labels and not column headers (and 1,2 are labels & not
rows)

=INDEX(A13,MATCH(2,A1:A3,0),MATCH("b",A11,0))

if they are column headers and rows use

=INDEX(A1:C2,2,2)
where the first 2 is the row number and the second 2 is the column number of
the range specified in the first parameter.

Cheers
Julied

 

[Bernie Deitrick]

Jeppe,

If you table is in A14, A11 has a 2, and A12 has b:

=INDEX(A14,MATCH(A11,A1:A4,FALSE),MATCH(A12,A11,FALSE))

HTH,
Bernie
MS Excel MVP

 

[Bernie Deitrick]

Of course, I made up an example with one too many rows: You could use this.

=INDEX(A13,MATCH(A11,A1:A3,FALSE),MATCH(A12,A11,FALSE))

And then I realized that you posted to programming. With the same layout:

Dim myVal As Variant

myVal = Range("A13")(Application.Match( _
Range("A11").Value, Range("A1:A3"), False), _
Application.Match(Range("A12").Value, _
Range("A11"), False))
MsgBox myVal

HTH,
Bernie
MS Excel MVP

Jeppe,

If you table is in A14, A11 has a 2, and A12 has b:

=INDEX(A14,MATCH(A11,A1:A4,FALSE),MATCH(A12,A11,FALSE))

HTH,
Bernie
MS Excel MVP

 

[Guest]

Jeppe,

Here's one idea Using a combination of offset and match functions:

Define the range of your matrix as "rngDatabase"
Define the column containing the row header as "xCoordinate"
Define the row containing the column header as "yCoordinate"
Define the cell to contain your x criteria as "rngX_Value"
Define the cell to contain your y criteria as "rngY_Value"

Here's the formula (assumes headers are top row and left most column):
=OFFSET(rngDatabase,MATCH(rngX_Value,xCoordinate,0)-1,MATCH(rngY_Value,yCoordinate,0)-1,1,1)

Dale Preuss