software

Hello,

I've been trying to do a Vlookup over multiple worksheets. Can this be
done without a macro?
I've tried doing
VLOOKUP(a1,'x'!D 'y'!D,1,false)
Can someone please tell me if this is even the correct syntax

Many thanks for any help

Max--
Max_power
------------------------------------------------------------------------
Max_power's Profile: www.excelforum.com/member.php...oamp;userid=32255
View this thread: www.excelforum.com/showthread...hreadid=524715
I apologise for double posting.

I've updated my forumula a little but I'm still getting errors.

I want to look up a number in worksheet x and in worksheet y and then
return a nan if its not found
Here's my code

IF(ISNA(VLOOKUP(a1,'x'!, z, false)), (VLOOKUP(a1,'y'!, z, false)),
(VLOOKUP(a1,'x'!, z, false)))

I'm still getting some critical errors though

Many thanks again

Max--
Max_power
------------------------------------------------------------------------
Max_power's Profile: www.excelforum.com/member.php...oamp;userid=32255
View this thread: www.excelforum.com/showthread...hreadid=524715VLOOKUP data must be in a continuous range (may not span over 2 worksheets)

HTH
--
AP

quot;Max_powerquot; gt; a écrit
dans le message de
...
gt;
gt; Hello,
gt;
gt; I've been trying to do a Vlookup over multiple worksheets. Can this be
gt; done without a macro?
gt; I've tried doing
gt; VLOOKUP(a1,'x'!D 'y'!D,1,false)
gt; Can someone please tell me if this is even the correct syntax
gt;
gt; Many thanks for any help
gt;
gt; Max
gt;
gt;
gt; --
gt; Max_power
gt; ------------------------------------------------------------------------
gt; Max_power's Profile:
www.excelforum.com/member.php...oamp;userid=32255
gt; View this thread: www.excelforum.com/showthread...hreadid=524715
gt;
one possibility is:

IF(NOT(ISNA(VLOOKUP(A1,'x'!, z,FALSE))),VLOOKUP(A1,'x'!,
z,FALSE),IF(ISNA(VLOOKUP(A1,'x'!, z,FALSE)),quot;Not in either
sheetquot;,VLOOKUP(A1,'x'!, z,FALSE)))

hth.

cheers.

quot;Max_powerquot; wrote:

gt;
gt; I apologise for double posting.
gt;
gt; I've updated my forumula a little but I'm still getting errors.
gt;
gt; I want to look up a number in worksheet x and in worksheet y and then
gt; return a nan if its not found
gt; Here's my code
gt;
gt; IF(ISNA(VLOOKUP(a1,'x'!, z, false)), (VLOOKUP(a1,'y'!, z, false)),
gt; (VLOOKUP(a1,'x'!, z, false)))
gt;
gt; I'm still getting some critical errors though
gt;
gt; Many thanks again
gt;
gt; Max
gt;
gt;
gt; --
gt; Max_power
gt; ------------------------------------------------------------------------
gt; Max_power's Profile: www.excelforum.com/member.php...oamp;userid=32255
gt; View this thread: www.excelforum.com/showthread...hreadid=524715
gt;
gt;


You colud try to take this formula below as example.
=VLOOKUP(A5,INDIRECT(quot;'quot;amp;INDEX(H2:H9,MATCH(TRUE,CO UNTIF(INDIRECT(quot;Sheetquot;amp;{2,3,4,5,6,7,8,9}amp;quot;!A4:A100 0quot;),A5)gt;0,0))amp;quot;'!A4:B1000quot;),2,0)

remeber in column H2:H9 list your worksheet names.--
vane0326
------------------------------------------------------------------------
vane0326's Profile: www.excelforum.com/member.php...oamp;userid=14731
View this thread: www.excelforum.com/showthread...hreadid=524715
Thank you very much for the reply.
I may sound stupid but I couldn't get either formula to work.
Say I had 3 worksheets, sheet 1,2,3.
I'm currently working in worksheet 3 but I want to look up numbers in
sheets 2 and 3 and return a nan if not found.

Can you please explain to me how I can apply either of the above
formulas for that purpose. I'm quite new to excel and I greatly
appreciate any help on this matter.

Many thanks

Max--
Max_power
------------------------------------------------------------------------
Max_power's Profile: www.excelforum.com/member.php...oamp;userid=32255
View this thread: www.excelforum.com/showthread...hreadid=524715hi max:

i've saved a simple example he

www.savefile.com/files.php?fid=2772276

hth.

cheers.

quot;Max_powerquot; wrote:

gt;
gt; Thank you very much for the reply.
gt; I may sound stupid but I couldn't get either formula to work.
gt; Say I had 3 worksheets, sheet 1,2,3.
gt; I'm currently working in worksheet 3 but I want to look up numbers in
gt; sheets 2 and 3 and return a nan if not found.
gt;
gt; Can you please explain to me how I can apply either of the above
gt; formulas for that purpose. I'm quite new to excel and I greatly
gt; appreciate any help on this matter.
gt;
gt; Many thanks
gt;
gt; Max
gt;
gt;
gt; --
gt; Max_power
gt; ------------------------------------------------------------------------
gt; Max_power's Profile: www.excelforum.com/member.php...oamp;userid=32255
gt; View this thread: www.excelforum.com/showthread...hreadid=524715
gt;
gt;


Thank you very much for all your help

Max--
Max_power
------------------------------------------------------------------------
Max_power's Profile: www.excelforum.com/member.php...oamp;userid=32255
View this thread: www.excelforum.com/showthread...hreadid=524715