can vlookup search multiple data tables

[Guest]

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

 

[Biff]

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

 

[Guest]

the reference verson of the index function can look in non adajacent
ranges,not sure if that extends to seperate worksheets tho

 

[Dave Peterson]

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

 

[Guest]

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]

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

 

[Guest]

Of course! thanks heaps.
cheers,
Nadia

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