Skip navigation

VLOOKUP across multiple worksheets

2484 Views 13 Replies Latest reply: Jul 2, 2013 10:18 AM by Yellowbox RSS
captainKCF Level 1 Level 1 (0 points)
Currently Being Moderated
Mar 14, 2011 9:47 AM
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.
  • Badunit Level 6 Level 6 (10,765 points)
    Currently Being Moderated
    Mar 14, 2011 10:18 AM (in response to captainKCF)
    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.
    Mac Pro 2008/Intel iMac 2009/Macbook Pro, Mac OS X (10.6.6)
  • jaxjason Level 4 Level 4 (3,320 points)
    Currently Being Moderated
    Mar 14, 2011 11:20 AM (in response to Badunit)
    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
    Mac mini, iPhone, iPad, Mac OS X (10.6.4)
  • Badunit Level 6 Level 6 (10,765 points)
    Currently Being Moderated
    Mar 14, 2011 6:25 PM (in response to captainKCF)
    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.
    Mac Pro 2008/Intel iMac 2009/Macbook Pro, Mac OS X (10.6.6)
  • Level 8 Level 8 (41,760 points)
    Currently Being Moderated
    Mar 15, 2011 6:12 AM (in response to captainKCF)
    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
    To be the AW6 successor, iWork MUST integrate a TRUE DB, not a list organizer !, Mac OS X (10.6.6)
  • Barry Fass-Holmes Level 6 Level 6 (11,895 points)
    Currently Being Moderated
    Jul 1, 2013 1:01 PM (in response to jaxjason)

    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!

  • Jerrold Green1 Level 7 Level 7 (28,200 points)
    Currently Being Moderated
    Jul 1, 2013 2:46 PM (in response to Barry Fass-Holmes)

    Barry,

     

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

     

    What is it that you are trying to do?

     

    Jerry

  • Barry Fass-Holmes Level 6 Level 6 (11,895 points)
    Currently Being Moderated
    Jul 1, 2013 3:29 PM (in response to Jerrold Green1)

    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

  • Yellowbox Level 4 Level 4 (3,920 points)
    Currently Being Moderated
    Jul 1, 2013 7:59 PM (in response to Barry Fass-Holmes)

    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.

  • Barry Fass-Holmes Level 6 Level 6 (11,895 points)
    Currently Being Moderated
    Jul 2, 2013 9:38 AM (in response to Yellowbox)

    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

  • Yellowbox Level 4 Level 4 (3,920 points)
    Currently Being Moderated
    Jul 2, 2013 10:18 AM (in response to Barry Fass-Holmes)

    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.

Actions

More Like This

  • Retrieving data ...

Bookmarked By (0)

Legend

  • This solved my question - 10 points
  • This helped me - 5 points
This site contains user submitted content, comments and opinions and is for informational purposes only. Apple disclaims any and all liability for the acts, omissions and conduct of any third parties in connection with or related to your use of the site. All postings and use of the content on this site are subject to the Apple Support Communities Terms of Use.