13 Replies Latest reply: Jul 2, 2013 10:18 AM by Yellowbox
captainKCF Level 1 Level 1 (0 points)
Hello!

I am trying to be able to search for a student name in a specific table in 4 different worksheets... Example: If John Doe isnt in worksheet 1, then check worksheet 2...

I came up with the following, which does work if the person is in the first worksheet.

Thanks!!

=IF(ISBLANK(VLOOKUP($A3,Lincoln Data E::Student Grades :: $A$1:$I$26,COLUMN()+1,FALSE)),IF(ISBLANK(VLOOKUP($A3,Lincoln Data F::Student Grades :: $A$1:$I$26,COLUMN()+1,FALSE)),IF(ISBLANK(VLOOKUP($A3,Lincoln Data G::Student Grades :: $A$1:$I$26,COLUMN()+1,FALSE)),IF(ISBLANK(VLOOKUP($A3,Lincoln Data H::Student Grades :: $A$1:$I$26,COLUMN()+1,FALSE)),VLOOKUP($A3,Lincoln Data H::Student Grades :: $A$1:$I$26,COLUMN()+1,FALSE)),VLOOKUP($A3,Lincoln Data G::Student Grades :: $A$1:$I$26,COLUMN()+1,FALSE)),VLOOKUP($A3,Lincoln Data F::Student Grades :: $A$1:$I$26,COLUMN()+1,FALSE)),VLOOKUP($A3,Lincoln Data E::Student Grades :: $A$1:$I$26,COLUMN()+1,FALSE))

Macbook pro, Mac OS X (10.6.6), I Love It.
  • 1. Re: VLOOKUP across multiple worksheets
    Badunit Level 6 Level 6 (10,815 points)
    I have not analyzed your formula or tried to think of a better way to do what you want but I think you need to replace ISBLANK with ISERROR for it to work. If it does not find the name in one of the tables, the result is an error, not a blank.
  • 2. Re: VLOOKUP across multiple worksheets
    jaxjason Level 4 Level 4 (3,325 points)
    I also think replacing the IF's with IFERROR's would make the whole thing more readable.

    =iferror(vlookup1,iferror(vlookup2,iferror(vlookup3,"Not found")))


    Jason
  • 3. Re: VLOOKUP across multiple worksheets
    captainKCF Level 1 Level 1 (0 points)
    Thanks for the quick response! That makes sense. So it partially worked too. I can now query a name from the first 3 worksheets, but if I query one from the 4th I get FALSE.

    It appears that whatever query is in the "middle" gives the false. I added in one more worksheet, placed it as the last IF and now my 4 original work but the 5th wont.

    Any ideas?

    Thanks again!

    Newest function:

    =IF(ISERROR(VLOOKUP($A3,Lincoln Data E::Student Grades :: $A$1:$I$26,COLUMN()+1,FALSE)),IF(ISERROR(VLOOKUP($A3,Lincoln Data F::Student Grades :: $A$1:$I$26,COLUMN()+1,FALSE)),IF(ISERROR(VLOOKUP($A3,Lincoln Data G::Student Grades :: $A$1:$I$26,COLUMN()+1,FALSE)),IF(ISERROR(VLOOKUP($A3,Lincoln Data H::Student Grades :: $A$1:$I$26,COLUMN()+1,FALSE)),IF(ISERROR(VLOOKUP($A3,HMS Data::Student Grades :: $A$1:$I$26,COLUMN()+1,FALSE)),VLOOKUP($A3,HMS Data::Student Grades :: $A$1:$I$26,COLUMN()+1,FALSE)),VLOOKUP($A3,Lincoln Data H::Student Grades :: $A$1:$I$26,COLUMN()+1,FALSE)),VLOOKUP($A3,Lincoln Data G::Student Grades :: $A$1:$I$26,COLUMN()+1,FALSE)),VLOOKUP($A3,Lincoln Data F::Student Grades :: $A$1:$I$26,COLUMN()+1,FALSE)),VLOOKUP($A3,Lincoln Data E::Student Grades :: $A$1:$I$26,COLUMN()+1,FALSE))

    Message was edited by: captainKCF
  • 4. Re: VLOOKUP across multiple worksheets
    Badunit Level 6 Level 6 (10,815 points)
    The IFERROR idea would be cleaner and shorter I think.

    But, to answer your question, starting in the middle of your formula you have
    ...IF(ISERROR(VLOOKUP(...HMS DATA...)), VLOOKUP(...HMS Data...),...
    In other words, if the lookup is an error, you do the lookup again (which, of course, will be an error). This is where the FALSE result should be, not at the end of the formula.
    ...IF(ISERROR(VLOOKUP(...HMS DATA...)), FALSE, VLOOKUP(...HMS Data...),...

    I recommend the cleaner and shorter IFERROR version posted by Jason. It requires half the lookups and is much easier to add onto.
  • 5. Re: VLOOKUP across multiple worksheets
    captainKCF Level 1 Level 1 (0 points)
    Thanks! The IFERROR is much cleaner now. I am glad to understand why my original wasnt functioning on all cylinders.

    Is this limited to 5 internal calls?
  • 6. Re: VLOOKUP across multiple worksheets
    Level 8 Level 8 (41,760 points)
    It seems that there is a limit to the length of a formula.
    I guess that it's linked to the size of the dedicated buffer receiving this one to parse it.

    If I am right, using shorter table and sheet names may be efficient.

    I guess that you don't rule teachers's grades so maybe you may replace "Students Grades" by "Grades"

    In the names of sheets, I guess that you know that they are storing datas, so maybe you may remove this word.

    You may also replace the ranges descriptors $A$1:$I$26 by the shorter ones $A:$I.

    Yvan KOENIG (VALLAURIS, France) mardi 15 mars 2011 14:12:36
  • 7. Re: VLOOKUP across multiple worksheets
    captainKCF Level 1 Level 1 (0 points)
    Thanks!
  • 8. Re: VLOOKUP across multiple worksheets
    Barry Fass-Holmes Level 6 Level 6 (11,895 points)

    Hi,

     

    Could someone please clarify Jason's forumula?

     

    =iferror(vlookup1,iferror(vlookup2,iferror(vlookup3,"Not found")))

     

    I tried to model after it, and it returns a syntax error.

     

    Here is mine.

     

    =IFERROR(VLOOKUPE2,'raw data—table123 6-27-13' :: Campus Id,IFERROR(VLOOKUPE2,raw data GR EAP—table124 :: $E$2:$E$3,IFERROR(VLOOKUPE2,'table125 intnl org 6-18-13' :: $D$2:$D$19,"Not found")))

     

    E2 is the current table's cell used for matching.

     

    Thanks in advance!

  • 9. Re: VLOOKUP across multiple worksheets
    Jerrold Green1 Level 7 Level 7 (28,995 points)

    Barry,

     

    I couldn't defend Jason's formula. It's a rather unconventional one.

     

    What is it that you are trying to do?

     

    Jerry

  • 10. Re: VLOOKUP across multiple worksheets
    Barry Fass-Holmes Level 6 Level 6 (11,895 points)

    Hi Jerry,

     

    Thanks for your posting.

     

    The goal is to match the value in a worksheet's cell E2 with the values in a specific column (ID) in three other worksheets in the same Numbers file.

     

    Hope that clarifies.

     

    Barry

  • 11. Re: VLOOKUP across multiple worksheets
    Yellowbox Level 5 Level 5 (4,580 points)

    Hi Barry,

     

    I think Jason's formula contains some shorthand. VLOOKUP1 is not a function. I think Jason was implying that is the first VLOOKUP.

     

    So, in your model, VLOOKUPE2 should be:

     

    VLOOKUP(E2,<columns range>,<return column>,<close-match or exact-match>)

     

    The Function Browser in Numbers

     

    Regards,

    Ian.

  • 12. Re: VLOOKUP across multiple worksheets
    Barry Fass-Holmes Level 6 Level 6 (11,895 points)

    Hi Ian,

     

    Many thanks for your post.

     

    Yes, I understood that Jason's posting contained pseudocode.

     

    Thanks for the helpful pointer to the Function Browser, which reminded me that I left out components in the formula.

     

    An additional problem with my formula was that it needed a close parenthesis (set in red typeface below) after each of its components. Without those close parentheses (in addition to the three after "Not found,"), the formula returned an error.

     

    The following revision worked properly. E2 is the cell value in the current worksheet to be matched with the three other worksheets that are in the same Numbers file.

     

    =IFERROR(VLOOKUP(E2,'raw data—table123 6-27-13' :: $G$2:$G$1204,1,0),IFERROR(VLOOKUP(E2,raw data GR EAP—table124 :: $E$2:$E$3,1,0),IFERROR(VLOOKUP(E2,'table125 intnl org 6-18-13' :: $D$2:$D$19,1,0),"Not found")))

     

    Thanks again!

     

    Barry

  • 13. Re: VLOOKUP across multiple worksheets
    Yellowbox Level 5 Level 5 (4,580 points)

    Hi Barry,

     

    Thanks for the feedback. Oh, matching those parentheses! I prefer to break long formulas into small bites in separate Columns. That makes it easier to track the errors. Hide the intermediate Columns when it works.

     

    I seem to recall that when using HyperTalk scripting in HyperCard, the editor refused to automatically indent a line in the script if there was an error. But that was a long time ago. 20/20 hindsight through rose-coloured glasses...

     

    Regards,

    Ian.