mulitple worksheets vlookup
 

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

mulitple worksheets vlookup
 

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

mulitple worksheets vlookup
 

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

HTH
--
AP

"Max_power" a écrit
dans le message de
...

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

mulitple worksheets vlookup
 

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

mulitple worksheets vlookup
 

You colud try to take this formula below as example.



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

remeber in column H2:H9 list your worksheet names.


--
vane0326
------------------------------------------------------------------------
vane0326's Profile: http://www.excelforum.com/member.php...o&userid=14731
View this thread: http://www.excelforum.com/showthread...hreadid=524715

mulitple worksheets vlookup
 

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

mulitple worksheets vlookup
 

hi max:

i've saved a simple example he

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

mulitple worksheets vlookup
 

Thank you very much for all your 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