mulitple worksheets vlookup

  • Thread starter Thread starter Max_power
  • Start date Start date
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
 
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
 
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.
 
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.
 
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
 
Back
Top