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
a500505077Asked:
Who is Participating?
 
Wilder1626Commented:
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")))))

Open in new window

Lookup-no-3.xlsx
0
 
Wilder1626Commented:
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
 
Wilder1626Commented:
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))

Open in new window

0
 
Wilder1626Commented:
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)

Open in new window

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

All Courses

From novice to tech pro — start learning today.