Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
rounding numbers for match function | Excel Worksheet Functions | |||
Excel option to store trendline's coefficients in cells for use | Charts and Charting in Excel | |||
VlLOOKUP function with MATCH | Excel Worksheet Functions | |||
I want to use the MATCH function with the AVERAGE function but I . | Excel Worksheet Functions | |||
Match function selecting first value it matches on exactly | Excel Worksheet Functions |