help on match function
 

I have a matrix of data with headers at first row and first column.
some rows below I have put in on of the row header and column header.
whenever i change the row and column header, I should get the right
data coresponding the header row and header column.
e.g.

let this be the data :
29/04/2006 30/04/2006 01/05/2006 02/05/2006 03/05/2006 04/05/2006 05/05/2006
a 531 75 389 35 34 34 453
b 351 78 7 34 534 56 753
c 315351 78 27 34 534 4753 45
d 722 578 73 3 53 43 3
e 7 2 7 42 43 4 423
f 2 4 27 31 42 24 42
g 55 45 454 23 4727 56 34
h 5 67 24 53 254 76 3
i 7 35 3 453 23 7 42
j 56 389 7 45 45 63 34

so when i type date 29/4/2006 in cell b20 and "j" in cell a21, I
should get the value 56 in cell b21. I hope I was clear in explaining
my problem. there is a combination of function using match and index
functions to solve this but i do not remember that somehow .. please
help me..


--
vishal
------------------------------------------------------------------------
vishal's Profile: http://www.excelforum.com/member.php...fo&userid=5327
View this thread: http://www.excelforum.com/showthread...hreadid=537421

help on match function
 

=INDEX(A1:H11,MATCH(A21,A1:A11,0),MATCH(B20,A1:H1, 0))

Assuming that your table (as it's written) starts at A1.

Ian

"vishal" wrote:


I have a matrix of data with headers at first row and first column.
some rows below I have put in on of the row header and column header.
whenever i change the row and column header, I should get the right
data coresponding the header row and header column.
e.g.

let this be the data :
29/04/2006 30/04/2006 01/05/2006 02/05/2006 03/05/2006 04/05/2006 05/05/2006
a 531 75 389 35 34 34 453
b 351 78 7 34 534 56 753
c 315351 78 27 34 534 4753 45
d 722 578 73 3 53 43 3
e 7 2 7 42 43 4 423
f 2 4 27 31 42 24 42
g 55 45 454 23 4727 56 34
h 5 67 24 53 254 76 3
i 7 35 3 453 23 7 42
j 56 389 7 45 45 63 34

so when i type date 29/4/2006 in cell b20 and "j" in cell a21, I
should get the value 56 in cell b21. I hope I was clear in explaining
my problem. there is a combination of function using match and index
functions to solve this but i do not remember that somehow .. please
help me..


--
vishal
------------------------------------------------------------------------
vishal's Profile: http://www.excelforum.com/member.php...fo&userid=5327
View this thread: http://www.excelforum.com/showthread...hreadid=537421

help on match function
 

Hi Vishal,

Something like

=INDEX(B2:H11,MATCH(B21,A2:A11),MATCH(B20,B1:H1))

But you have to correct the arrays..

Regards,
Bondi

help on match function
 

Hi Ian and Bondi,

Thanks a lot, your suggestions did work. I have got the solution.
regards,
Vishal


--
vishal
------------------------------------------------------------------------
vishal's Profile: http://www.excelforum.com/member.php...fo&userid=5327
View this thread: http://www.excelforum.com/showthread...hreadid=537421