I actualy did, this is why there is right formula - so I extrct only the last
4 digits of the cell dat (year). Did yu copy down the formula? Everywere is
the erro message?
--
Greatly appreciated
Eva


"Ksean" wrote:

Eva,

Your formula returns a #N/A.

This is the whole formula I used
=VLOOKUP(VALUE(RIGHT(VLOOKUP(A3,Registrants!B3:G80 0,6,FALSE),4)),Start!B26:E47,4,FALSE)

This part =VLOOKUP(A3,Registrants!B3:G800,6,FALSE) returns the anticipated
answer for this part of the equation.

I did notice that you did not appear to address the date to year issue where
column G on the 'Registrants' sheet has a whole date but only the year needs
to be extracted when checking column B on the 'Start' sheet.

Kerry

"Eva" wrote:

Try this formula
=VLOOKUP(VALUE(RIGHT(VLOOKUP(A2,Registrants!A:G,7, FALSE),4)),Start!B:E,4,FALSE)
and let me know if this is what you expected
Click yes if helped
--
Greatly appreciated
Eva


"Ksean" wrote:

Eva,

I hope this is what you are looking for. These are small excerpts of the
three spreadsheets. Column J on the €˜Financial spreadsheet is where I am
looking for answers.

Sheet €˜Financial
A J
3 92
4 103
5 356
6 15
7 9
8 225
9 116
10 18
11 302
12 78
13 3


Sheet €˜Registrants
B G
3 268 May. 8, 1996
4 32 Apr. 9, 1995
5 78 Jul. 7, 1999
6 125 Mar. 22, 1998
7 92 Apr. 8, 1996
8 18 May. 24, 1997
9 33 Sep. 29, 2000
10 356 Aug. 18, 1993
11 114 Feb. 28, 1995
12 116 Jan. 30, 1992
13 201 Jun. 29, 1994


Sheet €˜Start
B C D E
28 1991 18 Adult N
29 1992 17 U18 N
30 1993 16 U18 N
31 1994 15 U16 Y
32 1995 14 U16 Y
33 1996 13 U14 Y
34 1997 12 U14 Y
35 1998 11 U12 Y
36 1999 10 U12 Y
37 2000 9 U10 Y
38 2001 8 U10 Y


In this example the €˜Financial sheet cell J3 would return a €˜Y value, J5
would return a €˜N, J9 would return a €˜N, J10 would return a €˜Y and J12
would return a €˜Y.

Kerry


"Eva" wrote:

Can you show the sample of the data and how it should looks like?
--
Greatly appreciated
Eva


"Ksean" wrote:

With this problem I am dealing with a workbook that contains three
spreadsheets €˜Financial, €˜Registrants and €˜Start

The answer/formula is to be in cell J3 on sheet €˜Financial so Ill start
there now match the value in cell A3 on sheet €˜Financial to the value
somewhere in column B (B3:B800) on sheet €˜Registrants, once a match has been
found then go to column G on that row which has a date in it (i.e. May 8,
1996) and match only the year to the year somewhere in column B (B26:B47) of
sheet €˜Start, once a match has been found then go to column E on that row,
now display the contents of that cell back in cell J3 on sheet €˜Financial.

Thanks
Ksean