SOLVED

Help with Xlookup

%3CLINGO-SUB%20id%3D%22lingo-sub-1133756%22%20slang%3D%22en-US%22%3EHelp%20with%20Xlookup%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1133756%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20attempting%20to%20use%20the%20new%20XLookup%20instead%20of%20VLookup.%20I%20have%20formula%20as%3A%20%3Dxlookup(A17%2C%5BCTC_SIDEMP_CRSW_MAPPING_138.xlsx%5Dsheet1!%24A%242%3A%24A%2420292%2C%5BCTC_SIDEMP_CRSW_MAPPING_138.xlsx%5Dsheet1!%24B%242%3A%24B%242%3A%24B%2420292%2C0%2C2)%3CBR%20%2F%3EI'm%20getting%20a%20%23Names%3F%20error.%20Please%20help.%20As%20you%20can%20see%20these%20are%20very%20large%20worksheets.%3C%2FP%3E%3CP%3EI%20need%20to%20look%20up%20column%20A%20from%20one%20worksheet%20to%20return%20data%20from%20a%20different%20worksheet.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1133756%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1134284%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20Xlookup%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1134284%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F408611%22%20target%3D%22_blank%22%3E%40ktwogood253%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAre%20you%20an%20Office%20insider%3F%26nbsp%3B%20XLOOKUP%20is%20not%20yet%20in%20general%20release%20(It's%20coming%20very%20soon.).%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1134316%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20Xlookup%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1134316%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F428790%22%20target%3D%22_blank%22%3E%40Patrick2788%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOh.%20Well%20then%20I%20guess%20it%20is%20back%20to%20VLookup%20which%20is%20also%20not%20working.%20Anytime%20I%20try%20to%20go%20between%20two%20different%20workbooks%20I%20have%20this%20issue.%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20your%20response.%3C%2FP%3E%3CP%3EKari%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1134359%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20Xlookup%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1134359%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F408611%22%20target%3D%22_blank%22%3E%40ktwogood253%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EKari%2C%20is%20XLOOKUP%20available%20or%20not%20you%20may%20simply%20check%20starting%20to%20type%20%3DXLOO%20-%20function%20name%20will%20be%20prompted.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHere%20the%20error%20is%20at%20least%20in%20reference%20...%3CSPAN%3E%5BCTC_SIDEMP_CRSW_MAPPING_138.xlsx%5Dsheet1!%3CFONT%20color%3D%22%23FF0000%22%3E%3CSTRONG%3E%24B%242%3A%24B%242%3A%24B%2420292%3C%2FSTRONG%3E%3C%2FFONT%3E..%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1134386%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20Xlookup%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1134386%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F408611%22%20target%3D%22_blank%22%3E%40ktwogood253%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20think%20it's%20likely%20you%20don't%20have%20XLOOKUP%20yet.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETry%20this%20for%20VLOOKUP%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DVLOOKUP(A17%2C%5BCTC_SIDEMP_CRSW_MAPPING_138.xlsx%5Dsheet1!%24A%242%3A%24B%2420292%2C2%2C0)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1134404%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20Xlookup%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1134404%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F428790%22%20target%3D%22_blank%22%3E%40Patrick2788%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you!%26nbsp%3B%20I%20got%20it%20to%20work%20using%20the%20VLookup.%20Now%20to%20replicate%205%20more%20times%20for%20the%20other%20location%20workbooks.%26nbsp%3B%3C%2FP%3E%3CP%3EKari%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1279120%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20Xlookup%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1279120%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F408611%22%20target%3D%22_blank%22%3E%40ktwogood253%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20your%20problem%20%2C%20Vlookup%20is%20the%20best.%20Just%20try%20it%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fgyankosh.net%2Fmsexcel%2Fpractice%2Fvlookup-examples%2F%23gsc.tab%3D0%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fgyankosh.net%2Fmsexcel%2Fpractice%2Fvlookup-examples%2F%23gsc.tab%3D0%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eand%20if%20we%20talk%20about%20your%20formula%20it%20contains%20a%20syntax%20error.%3C%2FP%3E%3CP%3EEspecially%20with%20your%20file%20names.%3C%2FP%3E%3CP%3EKindly%20check%20it.%3C%2FP%3E%3CP%3EIt%20seems%20like%20you%20have%20entered%20them%20manually.%20To%20avoid%20the%20error%20you%20should%20use%20them%20by%20opening%20the%20sheets%20and%20then%20selecting%20the%20ranges.%20It%20avoids%20the%20error.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1134467%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20Xlookup%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1134467%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F408611%22%20target%3D%22_blank%22%3E%40ktwogood253%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou're%20welcome.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBTW%20this%20where%20they'll%20announce%20when%20XLOOKUP%20is%20released%3A%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fexcel-blog%2Fannouncing-xlookup%2Fba-p%2F811376%22%20target%3D%22_blank%22%3Ehttps%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fexcel-blog%2Fannouncing-xlookup%2Fba-p%2F811376%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20should%20be%20soon.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

I am attempting to use the new XLookup instead of VLookup. I have formula as: =xlookup(A17,[CTC_SIDEMP_CRSW_MAPPING_138.xlsx]sheet1!$A$2:$A$20292,[CTC_SIDEMP_CRSW_MAPPING_138.xlsx]sheet1!$B$2:$B$2:$B$20292,0,2)
I'm getting a #Names? error. Please help. As you can see these are very large worksheets.

I need to look up column A from one worksheet to return data from a different worksheet.

9 Replies
Highlighted

@ktwogood253 

Are you an Office insider?  XLOOKUP is not yet in general release (It's coming very soon.).

Highlighted

@Patrick2788 

Oh. Well then I guess it is back to VLookup which is also not working. Anytime I try to go between two different workbooks I have this issue. 

Thanks for your response.

Kari

Highlighted

@ktwogood253 

Kari, is XLOOKUP available or not you may simply check starting to type =XLOO - function name will be prompted.

 

Here the error is at least in reference ...[CTC_SIDEMP_CRSW_MAPPING_138.xlsx]sheet1!$B$2:$B$2:$B$20292..

Highlighted
Best Response confirmed by ktwogood253 (Occasional Contributor)
Solution

@ktwogood253 

I think it's likely you don't have XLOOKUP yet. 

 

Try this for VLOOKUP:

 

=VLOOKUP(A17,[CTC_SIDEMP_CRSW_MAPPING_138.xlsx]sheet1!$A$2:$B$20292,2,0)

 

 

Highlighted

@Patrick2788 

Thank you!  I got it to work using the VLookup. Now to replicate 5 more times for the other location workbooks. 

Kari

Highlighted

@ktwogood253 

You're welcome.

 

BTW this where they'll announce when XLOOKUP is released:

https://techcommunity.microsoft.com/t5/excel-blog/announcing-xlookup/ba-p/811376

 

It should be soon.

Highlighted

@ktwogood253 

For your problem , Vlookup is the best. Just try it

https://gyankosh.net/msexcel/practice/vlookup-examples/#gsc.tab=0

 

and if we talk about your formula it contains a syntax error.

Especially with your file names.

Kindly check it.

It seems like you have entered them manually. To avoid the error you should use them by opening the sheets and then selecting the ranges. It avoids the error.

Highlighted

XLOOKUP is still rolling out to all Insiders, so you may not see it quite yet. It will be available to everyone soon.

Highlighted

@Lewis-H 

If XLOOKUP is not available for some insider I'd recommend to send a frown since that's a bug. It has to be. At least on Windows Desktop.