|
VLOOKUP over three spreadsheets
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 |
VLOOKUP over three spreadsheets
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 |
VLOOKUP over three spreadsheets
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 |
VLOOKUP over three spreadsheets
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 |
VLOOKUP over three spreadsheets
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 |
VLOOKUP over three spreadsheets
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 |
VLOOKUP over three spreadsheets
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 "Eva" wrote: 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 |
VLOOKUP over three spreadsheets
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" wrote: 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 "Eva" wrote: 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 |
VLOOKUP over three spreadsheets
Fine, I am glad that I was able to help :)
-- Greatly appreciated Eva "Ksean" wrote: 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" wrote: 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 "Eva" wrote: 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 |
| All times are GMT +1. The time now is 04:39 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com