Search for values between two sheets

J

Joey

Hi again

I think I've confused everyone with the previous thread.

Is it at all possible to do something like a VLOOKUP or anythin
similar to search between sheets/ workbooks


I appreciate any help you can give guys
Joe
 
F

Frank Kabel

Hi Joey
I gave you one answer for two sheets. If you have several this becomes
more complicated. What do you want to return?
- text values
- numbers

For the latter one there could be a solution using SUMPRODUCT, etc.

So you may explain this with a little bit more detail :)
 
J

Joey

Thanks Frank

The return information could be either but I would like it to sho
"none" if the keyed info doesn't exist on the other sheets.

I tried the formula you sent earlier
=IF(ISNA(VLOOKUP(A1,'sheet1'A1:B100,2,0)),IF(ISNA(VLOOKUP(A1,'sheet2'A1:B100,2,0)),"none
,VLOOKUP(A1,'sheet2'A1:B100,2,0)),VLOOKUP(A1,'sheet1'A1:B100,2,0))

but unfortunately it comes up with #NAME? and I don't know what to do.


Cheers
Joe
 
F

Frank Kabel

Hi
sorry there was a typo in it. Try
=IF(ISNA(VLOOKUP(A1,'sheet1'!A1:B100,2,0)),IF(ISNA(VLOOKUP(A1,'sheet2'!
A1:B100,2,0)),"none"
,VLOOKUP(A1,'sheet2'!A1:B100,2,0)),VLOOKUP(A1,'sheet1'!A1:B100,2,0))

If you need more sheets you may try the following:
1. You may also try to download the free add-in Morefunc.xll
(http://longre.free.fr/english). The function THREED converts a 3D
array to a 2D array.

2. Try the following kind of formula
=VLOOKUP(A1,THREED('sheet1:sheet4'!A1:B100),2,0)
 
Top