Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
James
 
Posts: n/a
Default vlookup over 2 sheets?

I have to do a vlookup, but the Table_array data is spread over two
worksheets because of the amount of data. Is this possible?


  #2   Report Post  
Dave Peterson
 
Posts: n/a
Default

Just do it twice:

=IF(NOT(ISERROR(VLOOKUP(A1,Sheet2!A:B,2,FALSE))),
VLOOKUP(A1,Sheet2!A:B,2,FALSE),
VLOOKUP(A1,Sheet3!A:B,2,FALSE))

(all one cell)


James wrote:

I have to do a vlookup, but the Table_array data is spread over two
worksheets because of the amount of data. Is this possible?


--

Dave Peterson
  #3   Report Post  
James
 
Posts: n/a
Default

Thanks!!, now, will this also work, say if i had 4 sheets?
"Dave Peterson" wrote in message
...
Just do it twice:

=IF(NOT(ISERROR(VLOOKUP(A1,Sheet2!A:B,2,FALSE))),
VLOOKUP(A1,Sheet2!A:B,2,FALSE),
VLOOKUP(A1,Sheet3!A:B,2,FALSE))

(all one cell)


James wrote:

I have to do a vlookup, but the Table_array data is spread over two
worksheets because of the amount of data. Is this possible?


--

Dave Peterson



  #4   Report Post  
Ray A
 
Posts: n/a
Default

one way:
=IF(ISNA(VLOOKUP(A1,tableA,2,FALSE)),vlookkup(A1,t ableB,2,FALSE),VLOOKUP(A1,tableA,2,FALSE))
HTH

"James" wrote:

I have to do a vlookup, but the Table_array data is spread over two
worksheets because of the amount of data. Is this possible?



  #5   Report Post  
Biff
 
Posts: n/a
Default

Hi!

now, will this also work, say if i had 4 sheets?


Quit "stonewalling" and tell us how many sheets you actually have! <vbg

You could use a formula similar to that for 4 sheets but there is a better
way.

Tell us the true number of sheets you need for the lookup and then I'll show
you a better way! I don't have time right now but I'll be back later.

Biff

"James" wrote in message
...
Thanks!!, now, will this also work, say if i had 4 sheets?
"Dave Peterson" wrote in message
...
Just do it twice:

=IF(NOT(ISERROR(VLOOKUP(A1,Sheet2!A:B,2,FALSE))),
VLOOKUP(A1,Sheet2!A:B,2,FALSE),
VLOOKUP(A1,Sheet3!A:B,2,FALSE))

(all one cell)


James wrote:

I have to do a vlookup, but the Table_array data is spread over two
worksheets because of the amount of data. Is this possible?


--

Dave Peterson







  #6   Report Post  
James
 
Posts: n/a
Default

Thanks for your time Biff!
originally i had 2 sheets, but then i got more data handed to me, so i
thought what the heck, could this work with 4, but i couldn't get it to
work.

anyway, yes, i need to be able to source from 4 worksheets now.
hey, what does "<vbg" mean?

thanks again for looking at this!

James


"Biff" wrote in message
...
Hi!

now, will this also work, say if i had 4 sheets?


Quit "stonewalling" and tell us how many sheets you actually have! <vbg

You could use a formula similar to that for 4 sheets but there is a better
way.

Tell us the true number of sheets you need for the lookup and then I'll

show
you a better way! I don't have time right now but I'll be back later.

Biff

"James" wrote in message
...
Thanks!!, now, will this also work, say if i had 4 sheets?
"Dave Peterson" wrote in message
...
Just do it twice:

=IF(NOT(ISERROR(VLOOKUP(A1,Sheet2!A:B,2,FALSE))),
VLOOKUP(A1,Sheet2!A:B,2,FALSE),
VLOOKUP(A1,Sheet3!A:B,2,FALSE))

(all one cell)


James wrote:

I have to do a vlookup, but the Table_array data is spread over two
worksheets because of the amount of data. Is this possible?

--

Dave Peterson







  #7   Report Post  
Dave Peterson
 
Posts: n/a
Default

VBG = Very Big Grin

And Biff can show you a nice formula--if you share the worksheet names with him,
too.

James wrote:

Thanks for your time Biff!
originally i had 2 sheets, but then i got more data handed to me, so i
thought what the heck, could this work with 4, but i couldn't get it to
work.

anyway, yes, i need to be able to source from 4 worksheets now.
hey, what does "<vbg" mean?

thanks again for looking at this!

James

"Biff" wrote in message
...
Hi!

now, will this also work, say if i had 4 sheets?


Quit "stonewalling" and tell us how many sheets you actually have! <vbg

You could use a formula similar to that for 4 sheets but there is a better
way.

Tell us the true number of sheets you need for the lookup and then I'll

show
you a better way! I don't have time right now but I'll be back later.

Biff

"James" wrote in message
...
Thanks!!, now, will this also work, say if i had 4 sheets?
"Dave Peterson" wrote in message
...
Just do it twice:

=IF(NOT(ISERROR(VLOOKUP(A1,Sheet2!A:B,2,FALSE))),
VLOOKUP(A1,Sheet2!A:B,2,FALSE),
VLOOKUP(A1,Sheet3!A:B,2,FALSE))

(all one cell)


James wrote:

I have to do a vlookup, but the Table_array data is spread over two
worksheets because of the amount of data. Is this possible?

--

Dave Peterson





--

Dave Peterson
  #8   Report Post  
Biff
 
Posts: n/a
Default

Hi!

what does "<vbg" mean?


Very Big Grin

Quit "stonewalling" and tell us how many sheets you actually have! <vbg


That was a tongue-in-cheek way of "chastising" you for "changing the
parameters" of your post. This happens quite often. If posters would ask
their questions and tell us the "REAL DEAL" then multiple follow-ups would
not be needed.

I throw in the <vbg so that it's taken with a grain of salt!

OK, with 4 sheets to lookup that sort of falls into a gray area as to which
formula will be more efficient. You could use the formula Dave showed you:

=IF(NOT(ISERROR(VLOOKUP(A1,Sheet2!A:B,2,0))),VLOOK UP(A1,Sheet2!A:B,2,0),IF(NOT(ISERROR(VLOOKUP(A1,Sh eet3!A:B,2,0))),VLOOKUP(A1,Sheet3!A:B,2,0),IF(NOT( ISERROR(VLOOKUP(A1,Sheet4!A:B,2,0))),VLOOKUP(A1,Sh eet4!A:B,2,0),IF(NOT(ISERROR(VLOOKUP(A1,Sheet5!A:B ,2,0))),VLOOKUP(A1,Sheet5!A:B,2,0),""))))

But that's kind of a long formula. And if you have real long sheet names,
it'll be even longer! I HATE real long sheet names, by the way! <vbg "Long"
formulas tend to "scare" people!

Another method:

Enter the sheet names in a range, say, J1:J4 -

J1 = Sheet2
J2 = Sheet3
J3 = Sheet4
J4 = Sheet5

Now, give that range a defined name:

InsertNameDefine

Name: WsList
Refers to: =Sheet1$J$1:$J$4

Lookup formula entered as an array using the key combo of CTRL,SHIFT,ENTER:

=VLOOKUP(A1,INDIRECT("'"&INDEX(WsList,MATCH(TRUE,C OUNTIF(INDIRECT("'"&WsList&"'!A:A"),A1)0,0))&"'!A :B"),2,0)

Quite a bit shorter, ain't it!

The nested IF formula does a lookup on each sheet until it finds the lookup
value. The above formula does only a single lookup but it uses the Countif
and Index/Match functions to find which sheet to do the lookup on. If you
had more than 4 sheets to lookup then the above formula is the way to go.

The nested IF contains an error trap. The above does not. Here it is with an
error trap:

=IF(SUMPRODUCT(COUNTIF(INDIRECT("'"&WSlist&"'!A:A" ),A1)),VLOOKUP(A1,INDIRECT("'"&INDEX(WSlist,MATCH( TRUE,COUNTIF(INDIRECT("'"&WSlist&"'!A:A"),A1)0,0) )&"'!A:B"),2,0),"")

A little bit longer but still not a monster!

Biff

"James" wrote in message
...
Thanks for your time Biff!
originally i had 2 sheets, but then i got more data handed to me, so i
thought what the heck, could this work with 4, but i couldn't get it to
work.

anyway, yes, i need to be able to source from 4 worksheets now.
hey, what does "<vbg" mean?

thanks again for looking at this!

James


"Biff" wrote in message
...
Hi!

now, will this also work, say if i had 4 sheets?


Quit "stonewalling" and tell us how many sheets you actually have! <vbg

You could use a formula similar to that for 4 sheets but there is a
better
way.

Tell us the true number of sheets you need for the lookup and then I'll

show
you a better way! I don't have time right now but I'll be back later.

Biff

"James" wrote in message
...
Thanks!!, now, will this also work, say if i had 4 sheets?
"Dave Peterson" wrote in message
...
Just do it twice:

=IF(NOT(ISERROR(VLOOKUP(A1,Sheet2!A:B,2,FALSE))),
VLOOKUP(A1,Sheet2!A:B,2,FALSE),
VLOOKUP(A1,Sheet3!A:B,2,FALSE))

(all one cell)


James wrote:

I have to do a vlookup, but the Table_array data is spread over two
worksheets because of the amount of data. Is this possible?

--

Dave Peterson








  #9   Report Post  
Krishnakumar
 
Posts: n/a
Default


Hi,

Another option..

=LOOKUP(9.99999999999E+307,CHOOSE({1,2,3,4},VLOOKU P(A1,Sheet2!A:B,2,0),VLOOKUP(A1,Sheet3!A:B,2,0),VL OOKUP(A1,Sheet4!A:B,2,0),VLOOKUP(A1,Sheet5!A:B,2,0 )))

HTH


--
Krishnakumar


------------------------------------------------------------------------
Krishnakumar's Profile: http://www.excelforum.com/member.php...o&userid=20138
View this thread: http://www.excelforum.com/showthread...hreadid=467247

  #10   Report Post  
Dave Peterson
 
Posts: n/a
Default

Oh my gawd,

=IF(SUMPRODUCT(COUNTIF(INDIRECT("'"&WSlist&"'!A:A" ),A1)),VLOOKUP(A1,INDIRECT("'"&INDEX(WSlist,MATCH( TRUE,COUNTIF(INDIRECT("'"&WSlist&"'!A:A"),A1)0,0) )&"'!A:B"),2,0),"")

It's alive!!!!!!!!!!

<vvbg


  #11   Report Post  
James
 
Posts: n/a
Default

you guys are all so freaking cool!!
thanks for all of your suggestions. I'm going to try them all, hopefully
I'll learn something! <vbg

Thanks again guys!
"James" wrote in message
...
I have to do a vlookup, but the Table_array data is spread over two
worksheets because of the amount of data. Is this possible?




  #12   Report Post  
Biff
 
Posts: n/a
Default

Yeah, it's a thing of beauty!

For only 2,3,4 sheets I might opt for the nested IF.

Biff

"Dave Peterson" wrote in message
...
Oh my gawd,

=IF(SUMPRODUCT(COUNTIF(INDIRECT("'"&WSlist&"'!A:A" ),A1)),VLOOKUP(A1,INDIRECT("'"&INDEX(WSlist,MATCH( TRUE,COUNTIF(INDIRECT("'"&WSlist&"'!A:A"),A1)0,0) )&"'!A:B"),2,0),"")

It's alive!!!!!!!!!!

<vvbg



  #13   Report Post  
Biff
 
Posts: n/a
Default

That only works if the returned data is numeric.

Of course, if the data to return was TEXT then you could use:

=LOOKUP(REPT("Z",255)......................

But then once again, that'll only work for TEXT.

What if the data is mixed?

Biff

"Krishnakumar"
wrote in message
news:Krishnakumar.1vciik_1126695933.4335@excelforu m-nospam.com...

Hi,

Another option..

=LOOKUP(9.99999999999E+307,CHOOSE({1,2,3,4},VLOOKU P(A1,Sheet2!A:B,2,0),VLOOKUP(A1,Sheet3!A:B,2,0),VL OOKUP(A1,Sheet4!A:B,2,0),VLOOKUP(A1,Sheet5!A:B,2,0 )))

HTH


--
Krishnakumar


------------------------------------------------------------------------
Krishnakumar's Profile:
http://www.excelforum.com/member.php...o&userid=20138
View this thread: http://www.excelforum.com/showthread...hreadid=467247



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
calculating excel spreadsheet files for pensions and life insurance (including age calculation sheets) RICHARD Excel Worksheet Functions 1 March 15th 05 06:49 PM
Vlookup for two sheets in same workbook Roberta Excel Discussion (Misc queries) 3 February 23rd 05 01:29 PM
Vlookup from 2 different sheets WorkerBee Excel Worksheet Functions 20 February 21st 05 09:57 PM
vlookup data hidden within worksheet Excel Worksheet Functions 0 January 26th 05 01:09 PM
VLOOKUP across all sheets in a workbook Pawel P. Excel Worksheet Functions 3 January 13th 05 01:20 AM


All times are GMT +1. The time now is 09:18 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"