I have some date in Sheet1 of colom A, B, C..... like below
A B C
1. YR_2008 YR_2009 YR_2010
2. 04/01/2008 07/02/2009 12/01/2010
3. 17/02/2008 12/03/2009 17/02/2010
4. 19/03/2008 15/04/2009 03/04/2010
5. 10/04/2008 11/05/2009 21/05/2010
6. 05/05/2008 13/06/2009 30/06/2010

Now I have create name range like YR_2008 (i.e. A2:A20), YR_2009 (i.e. B2:B20), YR_2010 (i.e. C2:C20).

In F1 I have put an year 2010 to pull down the date of the year, so that I have used a formula in F2 like below

=hlookup(F1,"YR_"&F1,2,Flase)
result show "#Value!"

help me what should be the correct formula

Attached Files

Problem with HLOOKUP.xlsx (9.5 KB, 9 views)

Take a look at the attached.

Attached Files

Problem with HLOOKUP_2.xlsx (10.5 KB, 8 views)

Or, if you prefer HLOOKUP:

=HLOOKUP("YR_"&$F$1,$A$1:$C$6,$G$1,0)

with 2 in G1.