can vlookup search multiple data tables
 

I need to be able to do a vlookup to extract data that can be in 3 different
lists on 3 sheets... is this possible? BTW I cannot combine the data into one
list.
cheers
Nadia

Hi!

It can be done but it all depends.....

What exactly do you want to do? Lookup a value in sheet1, if not found then
lookup on sheet2, if not found lookup on sheet3?

Need more detail (and be explicit in your detail). Range locations, type of
data(text, numeric), sheet names. This type of detailed info eliminates
replies like:

"It can be done but it all depends....."

"What exactly do you want to do?"

<g

Biff

"Nadia" wrote in message
...
I need to be able to do a vlookup to extract data that can be in 3
different
lists on 3 sheets... is this possible? BTW I cannot combine the data into
one
list.
cheers
Nadia

the reference verson of the index function can look in non adajacent
ranges,not sure if that extends to seperate worksheets tho
--
paul
remove nospam for email addy!



"Biff" wrote:

Hi!

It can be done but it all depends.....

What exactly do you want to do? Lookup a value in sheet1, if not found then
lookup on sheet2, if not found lookup on sheet3?

Need more detail (and be explicit in your detail). Range locations, type of
data(text, numeric), sheet names. This type of detailed info eliminates
replies like:

"It can be done but it all depends....."

"What exactly do you want to do?"

<g

Biff

"Nadia" wrote in message
...
I need to be able to do a vlookup to extract data that can be in 3
different
lists on 3 sheets... is this possible? BTW I cannot combine the data into
one
list.
cheers
Nadia

Do you mean you want to return the first one it finds or you want to return all
3 that match?

I'm guessing the first one it finds...

You could use multiple =vlookup()'s in your formula:

=IF(NOT(ISERROR(VLOOKUP(A1,Sheet2!A:B,2,0))),VLOOK UP(A1,Sheet2!A:B,2,0),
IF(NOT(ISERROR(VLOOKUP(A1,Sheet3!A:B,2,0))),VLOOKU P(A1,Sheet3!A:B,2,0),
IF(NOT(ISERROR(VLOOKUP(A1,Sheet4!A:B,2,0))),VLOOKU P(A1,Sheet4!A:B,2,0),
"missing from all 3")))



Nadia wrote:

I need to be able to do a vlookup to extract data that can be in 3 different
lists on 3 sheets... is this possible? BTW I cannot combine the data into one
list.
cheers
Nadia

--

Dave Peterson

Thanks for all your responses.
The lookup value is a numeric code which appears only once on one of the 3
sheets, so your formula Dave would work well (havent tried yet but I was
thinking that way myself).... my other problem is that the range lookup will
change as data is dumped into those 3 sheets each week, guess its easy enough
to edit the data range each week but is there any other way or will this
involve code ("silly question")...of course it will!
thanks for your help

"Dave Peterson" wrote:

Do you mean you want to return the first one it finds or you want to return all
3 that match?

I'm guessing the first one it finds...

You could use multiple =vlookup()'s in your formula:

=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),"missing from all 3")))



Nadia wrote:

I need to be able to do a vlookup to extract data that can be in 3 different
lists on 3 sheets... is this possible? BTW I cannot combine the data into one
list.
cheers
Nadia

--

Dave Peterson

If there's nothing else on those sheets, just use the whole column. That's what
I did with sheet2!a:b.



Nadia wrote:

Thanks for all your responses.
The lookup value is a numeric code which appears only once on one of the 3
sheets, so your formula Dave would work well (havent tried yet but I was
thinking that way myself).... my other problem is that the range lookup will
change as data is dumped into those 3 sheets each week, guess its easy enough
to edit the data range each week but is there any other way or will this
involve code ("silly question")...of course it will!
thanks for your help

"Dave Peterson" wrote:

Do you mean you want to return the first one it finds or you want to return all
3 that match?

I'm guessing the first one it finds...

You could use multiple =vlookup()'s in your formula:

=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),"missing from all 3")))



Nadia wrote:

I need to be able to do a vlookup to extract data that can be in 3 different
lists on 3 sheets... is this possible? BTW I cannot combine the data into one
list.
cheers
Nadia

--

Dave Peterson


--

Dave Peterson

Of course! thanks heaps.
cheers,
Nadia

"Dave Peterson" wrote:

If there's nothing else on those sheets, just use the whole column. That's what
I did with sheet2!a:b.



Nadia wrote:

Thanks for all your responses.
The lookup value is a numeric code which appears only once on one of the 3
sheets, so your formula Dave would work well (havent tried yet but I was
thinking that way myself).... my other problem is that the range lookup will
change as data is dumped into those 3 sheets each week, guess its easy enough
to edit the data range each week but is there any other way or will this
involve code ("silly question")...of course it will!
thanks for your help

"Dave Peterson" wrote:

Do you mean you want to return the first one it finds or you want to return all
3 that match?

I'm guessing the first one it finds...

You could use multiple =vlookup()'s in your formula:

=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),"missing from all 3")))



Nadia wrote:

I need to be able to do a vlookup to extract data that can be in 3 different
lists on 3 sheets... is this possible? BTW I cannot combine the data into one
list.
cheers
Nadia

--

Dave Peterson


--

Dave Peterson