VLOOKUP over three spreadsheets

[Ksean]

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 I’ll 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

 

[Eva]

Can you show the sample of the data and how it should looks like?

 

[Ksean]

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]

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

 

[Ksean]

Eva,

Your formula returns a #N/A.

This is the whole formula I used
=VLOOKUP(VALUE(RIGHT(VLOOKUP(A3,Registrants!B3:G800,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]

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?

 

[Eva]

I checked it again and it really works on my end.
The formula is:
=VLOOKUP(VALUE(RIGHT(VLOOKUP(A2,Registrants!A:G,7,FALSE),4)),Start!B:E,4,FALSE)
Break down the formula and examine it.
so
this part returns year (in row2 = is 1996)
VALUE(RIGHT(VLOOKUP(A2,Registrants!A:G,7,FALSE),4))
and then:
VLOOKUP(.....,Start!B:E,4,FALSE) = and the rest of formula returns you the
answer (Y or N) in Start sheet.

Eva

 

[Ksean]

Eva,

I created a new workbook with three sheets in it. I labeled the sheets
'Start', 'Registrants' and 'Financial'.
On each of the sheets I place the appropriate table in the appropriate spot

Start sheet "1991" is in cell B28
Registrants sheet "268 is in cell B3 and May. 8, 1996 is in cell G3
Financial sheet 92 is in cell A3 and I placed the formula in cell J3

I made one minor correction in your formula
=VLOOKUP(VALUE(RIGHT(VLOOKUP(A2,Registrants!A:G,7,FALSE),4)),Start!B:E,4,FALSE) becomes
=VLOOKUP(VALUE(RIGHT(VLOOKUP(A3,Registrants!A:G,7,FALSE),4)),Start!B:E,4,FALSE) to reflect my tables.

I still get #N/A in cell J3

Then I changed the RIGHT into YEAR and adjusted the formula accordingly to
=VLOOKUP(VALUE(YEAR(VLOOKUP(A3,Registrants!$B:$G,6,FALSE))),Start!$B:$E,4,FALSE)

And now it works .

Thanks,
Kerry

 

[Eva]

Fine, I am glad that I was able to help