Lookup, return date from unknown column

[Akashwani]

Hi,

I'm going around in circles of frustration here.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Name	Animal	Amount	2000	2001	2002	2003	2004	2005	2006	2007	2008	2009	2010	2011	2012	2013	2014
2	Bill	Cat	100.00	*	*	*	*	*	*	*	*	Jan	*	*	*	Dec	*	*
3	Jo	Dog	250.00	*	*	*	Mar	*	*	Nov	*	*	*	*	*	*	*	*
4	Bob	mouse	350.00	*	*	*	*	*	*	*	*	*	*	*	*	Mar	*	Mar
5	Sue	Rabbit	500.00	Jan	*	*	Mar	*	*	*	*	*	*	*	*	*	*	*
6	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*
7	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*
8	Name	Animal	Amount	Year1	Year2	*	*	*	*	*	*	*	*	*	*	*	*	*
9	Jo	Dog	250.00	30/01/2008	31/12/2012	*	*	*	*	*	*	*	*	*	*	*	*	*
10	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*
11	Name	Animal	Amount	Year1	Year2	*	*	*	*	*	*	*	*	*	*	*	*	*
12	Sue	Rabbit	500.00	30/01/2000	31/03/2003	*	*	*	*	*	*	*	*	*	*	*	*	*
Sheet3

How do I return the values for D9:E9 or D12:E12?
I have tried numerous variations of Vlookup/Index/Match all without the desired result, obviously

The data in D2:R5 are true dates formatted to display the month, the data in row 1 are numbers, if that makes any difference!!

Thanks

Ak

 

[T. Valko]

Hi Biff,

I'm sorry if I'm causing you anything other than amusement
Your formula is perfect, thank you very much.

Once again, thanks to both of you.

Ak

You're welcome. Thanks for the feedback!

 

[markmzz]

Hi Mark,
I had to tweak your formulas and they also work perfectly, thanks.

=SMALL(INDIRECT("Sheet3!D"&MATCH(A9,Sheet3!$A$2:$A$5,0)+ROW(Sheet3!$A$1)&":R"&MATCH(A9,Sheet3!$A$2:$A$5,0)+ROW(Sheet3!$A$1)),1)

=LARGE(INDIRECT("Sheet3!D"&MATCH(A9,Sheet3!$A$2:$A$5,0)+ROW(Sheet3!$A$1)&":R"&MATCH(A9,Sheet3!$A$2:$A$5,0)+ROW(Sheet3!$A$1)),1)

Once again, thanks to both of you.

Ak

Akashwani,

Thanks for the feedback.

Markmzz