Index - Match Help

[andyt2005]

Hi all, Im after a bit of help with INDEX/Match.

I have a sheet with different months on different rows (In date formats)
and im trying to get index/match to search the whole table for a specific date then return a row underneath it.
I have never used Index/match before so at a bit of a loss.

Table example;

date/date/date/date etc.
data/data/data/data etc.
data/data/data/data etc.

date/date/date/date etc.
data/data/data/data etc.
data/data/data/data etc.

And so one

Thanks in advance.

 

[tybaltlives]

do your dates occur only once in the table?

 

[tybaltlives]

Because if the dates are unique and in sequential order you could use something like this. MATCH finds the location of the date typed into A1. A "1" is added to that date because you want the next line in the data and INDEX is used to return that date. This date appears in B1. Then VLOOKUP finds the data assocated with the date in B1.

Excel 2010
	A	B	C	D	E	F
1	1-Aug	9/15/2015	Data26	Data27	Data28	Data29
2
3	1-Jan	Data1	Data2	Data3	Data4	Data5
4	5-Apr	Data6	Data7	Data8	Data9	Data10
5	6-Jul	Data11	Data12	Data13	Data14	Data15
6	12-Jul	Data16	Data17	Data18	Data19	Data20
7	1-Aug	Data21	Data22	Data23	Data24	Data25
8	15-Sep	Data26	Data27	Data28	Data29	Data30
Sheet1

Cell Formulas
Range	Formula
B1	=INDEX(A3:A8,MATCH(A1,A3:A8,0)+1)
C1	=VLOOKUP($B$1,$A$3:$F$8,2)
D1	=VLOOKUP($B$1,$A$3:$F$8,3)
E1	=VLOOKUP($B$1,$A$3:$F$8,4)
F1	=VLOOKUP($B$1,$A$3:$F$8,5)

 

[andyt2005]

Yeah sorry all dates appear once. A sample of my table is below. So if I want to get the figure in the 3rd row for the 15/07 how would I go about it?

01/06	02/06	03/06	04/06	05/06	06/06	07/06	08/06	09/06	10/06	11/06	12/06	13/06	14/06	15/06	16/06	17/06	18/06	19/06	20/06	21/06	22/06	23/06	24/06	25/06
2	0													99										
														56										
									123					123										
																								
												July												
01/07	02/07	03/07	04/07	05/07	06/07	07/07	08/07	09/07	10/07	11/07	12/07	13/07	14/07	15/07	16/07	17/07	18/07	19/07	20/07	21/07	22/07	23/07	24/07	25/07
														555										
														66

 

[tybaltlives]

Your data is coming through a little weird. but try something like this:

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
10					=HLOOKUP(A12,INDIRECT("A"&14+B12-1&":Y"&14+B12-1+3),3)
11					"14" is the first row of your data. The "3" inside the INDIRECT statement indicates the number of rows you want your Hlookup to look.
12	15-Jul	7	66		The last "3" in the equation is the row you desired. You wanted the 3rd row. But it could be 2nd or 1st.
13					The "Y" is the last column of your data.
14	1-Jun	2-Jun	3-Jun	4-Jun	5-Jun	6-Jun	7-Jun	8-Jun	9-Jun	10-Jun	11-Jun	12-Jun	13-Jun	14-Jun	15-Jun	16-Jun	17-Jun
15	2	0													99
16	56
17		123	123
18
19	July
20	1-Jul	2-Jul	3-Jul	4-Jul	5-Jul	6-Jul	7-Jul	8-Jul	9-Jul	10-Jul	11-Jul	12-Jul	13-Jul	14-Jul	15-Jul	16-Jul	17-Jul
21															555
22															66
Sheet1

Cell Formulas
Range	Formula
C12	=HLOOKUP(A12,INDIRECT("A"&14+B12-1&":Y"&14+B12-1+3),3)
B12	{=MATCH(MONTH(A12),MONTH(A14:A20),0)}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[andyt2005]

Thank you that really helped me

 

[tybaltlives]

Happy to help. This board is a great resource.