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?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by