# Excel : VLOOKUP in Mutlisheets

Hi

I want to do a VLOOKUP function in Excel to find out code for each records searching in a different sheets with Multi sheets.

Files attached.
Copy-of-Copy-of-CRC-MISSING--2-.xlsx
CRC-All-Cog.xlsx
###### Who is Participating?

Commented:
after some testing, you should go with this one. It would not lag compare to the others, since you have a lot of records.

Paste this formula in cell B2, then going down.

``````=IFERROR(VLOOKUP(A2,Page1_1!A:B,2,0),IFERROR(VLOOKUP(A2,Page1_2!A:B,2,0),IFERROR(VLOOKUP(A2,Page1_3!A:B,2,0),IFERROR(VLOOKUP(A2,Page1_4!A:B,2,0),IFERROR(VLOOKUP(A2,Page1_5!A:B,2,0),"Don't_Exist")))))
``````
Lookup-no-3.xlsx
0

Commented:
Hi

Here is a example of VLOokup with multiple sheets.
You can put all sheets into 1 workbook and then, create your VLookup just like i show you on my example.
VLookup-with-multiple-sheets.xlsx
0

Commented:
This could be your formula once all sheets are in the same excel file.
``````=IF(ISNA(VLOOKUP(A2,Page1_1!A:B,2,0)),IF(ISNA(VLOOKUP(A2,Page1_2!A:B,2,0)),VLOOKUP(A2,Page1_3!A:B,2,0),VLOOKUP(A2,Page1_4!A:B,2,0)),VLOOKUP(A2,Page1_1!A:B,2,0))
``````
0

Commented:
Another way could be like this. Smaller formula at the same time.

Just need to put all sheets names into column A

``````=VLOOKUP(C2,INDIRECT("'"&INDEX(\$A\$2:\$A\$6,MATCH(TRUE,COUNTIF(INDIRECT("'"&\$A\$2:\$A\$6&"'!A:B"),C2)>0,0))&"'!A:B"),2,0)
``````
Lookup-no-2.xlsx
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.