mulitple worksheets vlookup

[Max_power]

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:D + 'y'!D:D,1,false)
Can someone please tell me if this is even the correct syntax

Many thanks for any help

Max

[Max_power]

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

[Ardus Petus]

VLOOKUP data must be in a continuous range (may not span over 2 worksheets)

HTH
--
AP

"Max_power" <[email protected]> a écrit
dans le message de
news:[email protected]...
>
> 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:D + 'y'!D: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:
http://www.excelforum.com/member.php...o&userid=32255
> View this thread: http://www.excelforum.com/showthread...hreadid=524715
>

[thechilipino]

one possibility is:

+IF(NOT(ISNA(VLOOKUP(A1,'x'!, z,FALSE))),VLOOKUP(A1,'x'!,
z,FALSE),IF(ISNA(VLOOKUP(A1,'x'!, z,FALSE)),"Not in either
sheet",VLOOKUP(A1,'x'!, z,FALSE)))

hth.

cheers.

"Max_power" wrote:

>
> 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: http://www.excelforum.com/member.php...o&userid=32255
> View this thread: http://www.excelforum.com/showthread...hreadid=524715
>
>

[vane0326]

You colud try to take this formula below as example.



=VLOOKUP(A5,INDIRECT("'"&INDEX(H2:H9,MATCH(TRUE,COUNTIF(INDIRECT("Sheet"&{2,3,4,5,6,7,8,9}&"!A4:A1000"),A5)>0,0))&"'!A4:B1000"),2,0)

remeber in column H2:H9 list your worksheet names.

[Max_power]

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

[thechilipino]

hi max:

i've saved a simple example here:

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

hth.

cheers.

"Max_power" wrote:

>
> 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: http://www.excelforum.com/member.php...o&userid=32255
> View this thread: http://www.excelforum.com/showthread...hreadid=524715
>
>

[Max_power]

Thank you very much for all your help

Max