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,

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

Are you looking for the column header dates for cells that are not empty?

So, the correct results for "Joe" "Dog" "250" would be 2003 and 2006?

 

[Akashwani]

Hi Biff,

Thanks for showing an interest.
I need to return the value on the same row, I've just realised I gave the wrong result in my previous sample data, sorry, that's how scrambled my brain is with this...

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/03/2003	31/11/2006	*	*	*	*	*	*	*	*	*	*	*	*	*
Sheet3

So, I need Jo, Dog, £250, Value of G3 and Value of J3.

Sorry about the confusion.

Ak

 

[T. Valko]

Hi Biff,

Thanks for showing an interest.
I need to return the value on the same row, I've just realised I gave the wrong result in my previous sample data, sorry, that's how scrambled my brain is with this...

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/03/2003	31/11/2006	*	*	*	*	*	*	*	*	*	*	*	*	*
Sheet3

So, I need Jo, Dog, £250, Value of G3 and Value of J3.

Sorry about the confusion.

Ak

Ok, so you DO want to do what I asked!

Try this array formula** entered in D9:

=INDEX($D$1:$R$1,SMALL(IF(INDEX($D$2:$R$5,MATCH(1,IF($A$2:$A$5=$A9,IF($B$2:$B$5=$B9,IF($C$2:$C$5=$C9,1))),0),0)<>"",COLUMN($D$2:$R$5)),COLUMNS($D9:D9))-COLUMN($D2)+1)

** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.

Copy across to E9.

 

[markmzz]

Hi Biff,

Thanks for showing an interest.
I need to return the value on the same row, I've just realised I gave the wrong result in my previous sample data, sorry, that's how scrambled my brain is with this...

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/03/2003	31/11/2006	*	*	*	*	*	*	*	*	*	*	*	*	*
Sheet3

So, I need Jo, Dog, £250, Value of G3 and Value of J3.

Sorry about the confusion.

Ak

Akashwani,

Or try this formulas (B9, C9, D9 and E9):

=INDEX($B$2:$B$5,MATCH(A9,$A$2:$A$5,0))

=INDEX($C$2:$C$5,MATCH(A9,$A$2:$A$5,0))

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

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


Markmzz

 

[Akashwani]

Hi Biff,

No, I'm not looking for the column header, I'm looking for the value below the header, sorry if I confused you.

Hi Mark.

Your formulas did the trick, thank you very much.

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	25/03/2003	30/11/2006	*	2003	*	*	*	*	*	*	*	*	*	*	*
Sheet3

Thanks to you both, I can now move on and try to do something simple
Stand by chaps, it's going to be a long night

Thanks again.

Ak

 

[Akashwani]

Hi Mark,

I have an after thought here!!
How would I apply your Small and Large formulas if I were looking up on a different sheet?

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	*	*	*	*	*	*	*	*	*	*	*
Sheet3

Excel Workbook
	A	B	C	D	E
8	Name	Animal	Amount	Year1	Year2
9	Jo	Dog	250	#N/A	#N/A
Sheet4

Thanks

Ak

 

[T. Valko]

Hi Biff,

No, I'm not looking for the column header, I'm looking for the value below the header, sorry if I confused you.

I've been confused a lot lately!

OK, then that makes things easier!

Array enterd** in D9:

=SMALL(INDEX($D$2:$R$5,MATCH(1,IF($A$2:$A$5=$A9,IF($B$2:$B$5=$B9,IF($C$2:$C$5=$C9,1))),0),0),COLUMNS($D9:D9))

** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.

Format as Date

Copy across to E9.

 

[markmzz]

Akashwani,

Try this in D9 and E9:

=SMALL(INDIRECT("Sheet3!D"&MATCH(A9,Sheet3!$A$2:$A$5,0)+ROW(Sheet3!$A$1)&":R"&MATCH(Sheet3!A9,$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(Sheet3!A9,$A$2:$A$5,0)+ROW(Sheet3!$A$1)),1)

Markmzz

 

[Akashwani]

Hi Biff,

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

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