Vlookup in workbook with 3 sheets and return corresponding cell data

[satish78]

Hi Friends,

I have workbook(masterdomainlist.xlsx) with 3 sheets (sheet1, sheet2, sheet3) with huge list of company names with location and domains.

Sheet1 contains
ColumnA (company name) ZzzipNet LLC
ColumnB (Location) CA
ColumnC (website domain) zzzip.net

same in sheet2 and sheet3

Here is the formula currently using in sheet4 to vlookup all 3 sheets in workbook(masterdomainlist)

{=IFERROR(VLOOKUP(A1,Sheet1!A:B,2,FALSE),IFERROR(VLOOKUP(A1,Sheet2!A:B,2,FALSE),IFERROR(VLOOKUP(A1,Sheet3!A:B,2,FALSE),"NA")))}

So, when I tried to open Masterdomainlist workbook it is taking lot of time to open with huge list and sometimes automatically excel shutdowns

I want to Vlookup in another workbook without opening Masterdomainlist workbook(includes 3 sheets data).

Help me with a formula

Thanks
Satish

 

[snjpverma]

Copy the formula in Notepad and then paste it into another excel. In new excel, Go to Edit Links in Data menu and click on change source. Then select the file .
Hope this helps

 

[satish78]

its not working with data source.

 

[seekerarcane]

Mention the Data range with Absolute criteria for for mentioned Sheets instead of just columns reference, like Sheet1!$A$2:$B$500

 

[satish78]

Mention the Data range with Absolute criteria for for mentioned Sheets instead of just columns reference, like Sheet1!$A$2:$B$500

Nope, My excel sheet got stopped and not working.
Could you paste the formula?

 

[satish78]

Here are files attached samples for review https://spaces.hightail.com/receive/SAixo

 

[FDibbins]

I have workbook(masterdomainlist.xlsx) with 3 sheets (sheet1, sheet2, sheet3) with huge list of company names with location and domains.

Sheet1 contains
ColumnA (company name) ZzzipNet LLC
ColumnB (Location) CA
ColumnC (website domain) zzzip.net

same in sheet2 and sheet3

Here is the formula currently using in sheet4 to vlookup all 3 sheets in workbook(masterdomainlist)

{=IFERROR(VLOOKUP(A1,Sheet1!A:B,2,FALSE),IFERROR(VLOOKUP(A1,Sheet2!A:B,2,FALSE),IFERROR(VLOOKUP(A1,Sheet3!A:B,2,FALSE),"NA")))}

1. I see no reason for that to be entered as an ARRAY formula (CTRL SHIFT ERTER), just enter in the normal way
2. Avoid using full-column references, just use the range you need
=IFERROR(VLOOKUP(A1,Sheet1!$A$1:$B$1000,2,FALSE),IFERROR(VLOOKUP(A1,Sheet2!$A$1:$B$1000,2,FALSE),IFERROR(VLOOKUP(A1,Sheet3!$A$1:$B$1000,2,FALSE),"NA")))