VLOOKUP across multiple worksheets

Question

Level 1

4 points

VLOOKUP across multiple worksheets

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.

Posted on Mar 14, 2011 9:47 AM

Reply

Answer 1

Badunit

Level 7

25,796 points

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.

Reply

Answer 2

jaxjason

Level 5

5,047 points

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

Reply

Answer 3

captainKCF Author

Level 1

4 points

Mar 14, 2011 4:13 PM in response to jaxjason

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

Reply

Answer 4

Badunit

Level 7

25,796 points

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.

Reply

Answer 5

captainKCF Author

Level 1

4 points

Mar 15, 2011 4:50 AM in response to Badunit

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?

Reply

Answer 6

KOENIG Yvan

Level 8

41,807 points

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

Reply

Answer 7

captainKCF Author

Level 1

4 points

Mar 15, 2011 2:39 PM in response to captainKCF

Thanks!

Reply

Answer 8

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!

Reply

Answer 9

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

Reply

Answer 10

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

Reply

Answer 11

Yellowbox

Level 7

20,383 points

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.

Reply

Answer 12

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

Reply

Answer 13

Yellowbox

Level 7

20,383 points

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.

Reply