Jan 28 2020 11:55 AM
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.
Jan 28 2020 12:50 PM
Are you an Office insider? XLOOKUP is not yet in general release (It's coming very soon.).
Jan 28 2020 12:53 PM
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
Jan 28 2020 01:00 PM
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..
Jan 28 2020 01:08 PM
SolutionI 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)
Jan 28 2020 01:12 PM
Thank you! I got it to work using the VLookup. Now to replicate 5 more times for the other location workbooks.
Kari
Jan 28 2020 01:29 PM
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.
Apr 03 2020 08:27 AM
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.
Apr 03 2020 09:27 AM
XLOOKUP is still rolling out to all Insiders, so you may not see it quite yet. It will be available to everyone soon.
Apr 03 2020 10:22 AM
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.
Jan 28 2020 01:08 PM
SolutionI 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)