Index Match and Horizontal Lookup

[CTMom]

I have table and need to look up date to match target date then return the number that is 6 rows below. I used HLOOKUP first but it kept putting number in every day not matching it to the correct day.

Can anyone tell me the best way of doing this?
Thanks

See below

Excel Workbook
	A	B	C	D	E
51	Monthly Schedule	01-Mar-11	01-Apr-11	01-May-11	01-Jun-11
52	land 1	285,000	-	-	-
53	land 2	-	892,000	-	-
54	land 3	-	-	1,236,600	-
55	land 4	-	-	-	-
56	Total land costs	285,000	892,000	1,236,600	-
57
58
59	Daily Schedule	31/03/2011	1/04/2011	2/04/2011	3/04/2011
60	Total land costs	285,000	892,000	892,000	892,000
Land

 

[Gerald Higgins]

Hi.

Sorry, I'm not clear exactly what you want.

Are you saying you want to match some date to the dates in row 51, and then lookup the value from 6 rows below, which would be row 57 (which seems to be empty) or maybe you mean row 56 ?

 

[Aladin Akyurek]

Do...

=HLOOKUP(B59,$B$51:$E$56,6,0)

yield what you want?

 

[T. Valko]

I have table and need to look up date to match target date then return the number that is 6 rows below. I used HLOOKUP first but it kept putting number in every day not matching it to the correct day.

Can anyone tell me the best way of doing this?
Thanks

See below

Excel Workbook
	A	B	C	D	E
51	Monthly Schedule	01-Mar-11	01-Apr-11	01-May-11	01-Jun-11
52	land 1	285,000	-	-	-
53	land 2	-	892,000	-	-
54	land 3	-	-	1,236,600	-
55	land 4	-	-	-	-
56	Total land costs	285,000	892,000	1,236,600	-
57
58
59	Daily Schedule	31/03/2011	1/04/2011	2/04/2011	3/04/2011
60	Total land costs	285,000	892,000	892,000	892,000
Land

Try this...

=INDEX($B56:$E56,MATCH(B59,$B53:$E53))

Copy across as needed.

 

[CTMom]

Thanks - worked!

 

[T. Valko]

Thanks - worked!

You're welcome. Thanks for the feedback!