Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to get the value in a matrix?
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.. -- Jepp ----------------------------------------------------------------------- Jeppe's Profile: http://www.excelforum.com/member.php...fo&userid=1121 View this thread: http://www.excelforum.com/showthread.php?threadid=27240 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to get the value in a matrix?
Hi Jeppe
one way assuming a, b, c are labels and not column headers (and 1,2 are labels & not rows) =INDEX(A1:D3,MATCH(2,A1:A3,0),MATCH("b",A1:D1,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 "Jeppe" wrote in message ... Below you see a matrix. I want to give a function i.e. the letter b and the digist 2 and then 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... -- Jeppe ------------------------------------------------------------------------ Jeppe's Profile: http://www.excelforum.com/member.php...o&userid=11218 View this thread: http://www.excelforum.com/showthread...hreadid=272406 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to get the value in a matrix?
Jeppe,
If you table is in A1:D4, A11 has a 2, and A12 has b: =INDEX(A1:D4,MATCH(A11,A1:A4,FALSE),MATCH(A12,A1:D 1,FALSE)) HTH, Bernie MS Excel MVP "Jeppe" wrote in message ... Below you see a matrix. I want to give a function i.e. the letter b and the digist 2 and then 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... -- Jeppe ------------------------------------------------------------------------ Jeppe's Profile: http://www.excelforum.com/member.php...o&userid=11218 View this thread: http://www.excelforum.com/showthread...hreadid=272406 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to get the value in a matrix?
Of course, I made up an example with one too many rows: You could use this.
=INDEX(A1:D3,MATCH(A11,A1:A3,FALSE),MATCH(A12,A1:D 1,FALSE)) And then I realized that you posted to programming. With the same layout: Dim myVal As Variant myVal = Range("A1:D3")(Application.Match( _ Range("A11").Value, Range("A1:A3"), False), _ Application.Match(Range("A12").Value, _ Range("A1:D1"), False)) MsgBox myVal HTH, Bernie MS Excel MVP "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Jeppe, If you table is in A1:D4, A11 has a 2, and A12 has b: =INDEX(A1:D4,MATCH(A11,A1:A4,FALSE),MATCH(A12,A1:D 1,FALSE)) HTH, Bernie MS Excel MVP "Jeppe" wrote in message ... Below you see a matrix. I want to give a function i.e. the letter b and the digist 2 and then 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... -- Jeppe ------------------------------------------------------------------------ Jeppe's Profile: http://www.excelforum.com/member.php...o&userid=11218 View this thread: http://www.excelforum.com/showthread...hreadid=272406 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to get the value in a matrix?
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 "Jeppe" wrote: Below you see a matrix. I want to give a function i.e. the letter b and the digist 2 and then 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... -- Jeppe ------------------------------------------------------------------------ Jeppe's Profile: http://www.excelforum.com/member.php...o&userid=11218 View this thread: http://www.excelforum.com/showthread...hreadid=272406 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Getting a value out of a Matrix | Excel Discussion (Misc queries) | |||
How can I transpose nXm matrix to mXn Matrix | Excel Worksheet Functions | |||
Matrix | Excel Worksheet Functions | |||
Matrix | Excel Discussion (Misc queries) | |||
BCG matrix | Charts and Charting in Excel |