VLOOKUP across multiple worksheets

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)

Like (0)


jaxjason Green Cove Springs, fl (Jax)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")))
JasonMac mini, iPhone, iPad, Mac OS X (10.6.4)
Like (0)


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: captainKCFMacbook Pro, Mac OS X (10.6.6), I Love It.
Like (0)


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)
Like (0)


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?Macbook, Mac OS X (10.5.4), I Love It.
Like (0)


KOENIG Yvan VALLAURIS (France)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:36To be the AW6 successor, iWork MUST integrate a TRUE DB, not a list organizer !, Mac OS X (10.6.6)
Like (0)



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 62713' :: Campus Id,IFERROR(VLOOKUPE2,raw data GR EAP—table124 :: $E$2:$E$3,IFERROR(VLOOKUPE2,'table125 intnl org 61813' :: $D$2:$D$19,"Not found")))
E2 is the current table's cell used for matching.
Thanks in advance!

Like (0)


Barry,
I couldn't defend Jason's formula. It's a rather unconventional one.
What is it that you are trying to do?
Jerry

Like (0)


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

Like (0)


Yellowbox New South Wales, Australia
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>,<closematch or exactmatch>)
The Function Browser in Numbers
Regards,
Ian.

Like (0)


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 62713' :: $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 61813' :: $D$2:$D$19,1,0),"Not found")))
Thanks again!
Barry

Like (0)


Yellowbox New South Wales, Australia
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 rosecoloured glasses...
Regards,
Ian.

Like (0)
