mulitple worksheets vlookup

M

Max_power

Hello,

I've been trying to do a Vlookup over multiple worksheets. Can this b
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

Ma
 
M

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 the
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

Ma
 
T

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.
 
V

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.
 
M

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
 
Top