J
James
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?
worksheets because of the amount of data. Is this possible?
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
D
Dave Peterson
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)
=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)
J
James
Thanks!!, now, will this also work, say if i had 4 sheets?
R
Ray A
one way
=IF(ISNA(VLOOKUP(A1,tableA,2,FALSE)),vlookkup(A1,tableB,2,FALSE),VLOOKUP(A1,tableA,2,FALSE))
HTH
=IF(ISNA(VLOOKUP(A1,tableA,2,FALSE)),vlookkup(A1,tableB,2,FALSE),VLOOKUP(A1,tableA,2,FALSE))
HTH
B
Biff
Hi!
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
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 said:Thanks!!, now, will this also work, say if i had 4 sheets?
J
James
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
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 said: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
D
Dave Peterson
VBG = Very Big Grin
And Biff can show you a nice formula--if you share the worksheet names with him,
too.
And Biff can show you a nice formula--if you share the worksheet names with him,
too.
B
Biff
Hi!
Very Big Grin
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))),VLOOKUP(A1,Sheet2!A:B,2,0),IF(NOT(ISERROR(VLOOKUP(A1,Sheet3!A:B,2,0))),VLOOKUP(A1,Sheet3!A:B,2,0),IF(NOT(ISERROR(VLOOKUP(A1,Sheet4!A:B,2,0))),VLOOKUP(A1,Sheet4!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:
Insert>Name>Define
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,COUNTIF(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
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))),VLOOKUP(A1,Sheet2!A:B,2,0),IF(NOT(ISERROR(VLOOKUP(A1,Sheet3!A:B,2,0))),VLOOKUP(A1,Sheet3!A:B,2,0),IF(NOT(ISERROR(VLOOKUP(A1,Sheet4!A:B,2,0))),VLOOKUP(A1,Sheet4!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:
Insert>Name>Define
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,COUNTIF(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
K
Krishnakumar
Hi,
Another option..
=LOOKUP(9.99999999999E+307,CHOOSE({1,2,3,4},VLOOKUP(A1,Sheet2!A:B,2,0),VLOOKUP(A1,Sheet3!A:B,2,0),VLOOKUP(A1,Sheet4!A:B,2,0),VLOOKUP(A1,Sheet5!A:B,2,0)))
HTH
Another option..
=LOOKUP(9.99999999999E+307,CHOOSE({1,2,3,4},VLOOKUP(A1,Sheet2!A:B,2,0),VLOOKUP(A1,Sheet3!A:B,2,0),VLOOKUP(A1,Sheet4!A:B,2,0),VLOOKUP(A1,Sheet5!A:B,2,0)))
HTH
D
Dave Peterson
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>
=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>
J
James
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!
thanks for all of your suggestions. I'm going to try them all, hopefully
I'll learn something! <vbg>
Thanks again guys!
B
Biff
Yeah, it's a thing of beauty!
For only 2,3,4 sheets I might opt for the nested IF.
Biff
For only 2,3,4 sheets I might opt for the nested IF.
Biff
B
Biff
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" <[email protected]>
wrote in message
news:[email protected]...
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" <[email protected]>
wrote in message
news:[email protected]...