help on match function

[vishal]

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

[Ian P]

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

[Bondi]

Hi Vishal,

Something like

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

But you have to correct the arrays..

Regards,
Bondi

[vishal]

Hi Ian and Bondi,

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