Xlookup formula data validation list

Question

Hello Peeps,

I am trying to use the following formula =XLOOKUP(F10,(employees[#Headers]),employees)&"" in a data validation list source but getting the following message error :

There is a problem with the formula.

Not trying to type a formula? ...

Could you help solving the issue with? Thanks

Answer 1

Thank you for responding. 

Since I didn’t have a sample file from you, I created one myself based on the formula you provided to test it. 

I’d like to confirm something: 

Did you copy the full formula =XLOOKUP(F10,(employees[#Headers]),employees)&"" directly into the “Source” field of the Data Validation list? 

If this is correct, that’s likely the issue. Excel doesn’t allow complex formulas like XLOOKUP to be used directly in the Data Validation source. 

For that reason, you can still use XLOOKUP(F10, ...), but instead of using employees[#Headers], try manually selecting the headers. For example, in my case, I used: 

• F10 contains the header name you want to look up (e.g., "Department") 
• {"Name","Department","Location"} is a manually typed array of headers 
• A2:C5 is the range of your employee data 

I tried successfully, and this approach avoids using structured table references and works better in formulas outside of Data Validation. 

Additionally, you can try this alternative method: 

Instead of placing the XLOOKUP formula directly into the Data Validation source, you can: 

1. You can use a regular XLOOKUP formula in a helper column to extract the values you want to appear in the dropdown list. 

For example: 

This will return the entire column that matches the header in F10. 

2. Then, you can use that output range as the source for your Data Validation list. 

I’ve tested this myself and it seems like this is the most effective way to avoid the limitations of using complex formulas directly in the Data Validation source. 

So, during your testing, if you run into any issues, feel free to reply here; I’ll do my best to support you within my scope. 

If you’d like to reach out to our higher technical support team, I can help guide you through that process. 

To do so, could you please confirm whether your account is a personal, school, or work account? 

Once I have that information, I’ll help guide you to teach out to the appropriate support channel. 

---

If you find this information helpful, please consider marking it as an answer. Once marked, it will automatically pin to top. As other users will also search information in this community, your valuable marked answer will definitely also help other users who have similar queries easily to find the correct channel and useful information more quickly.  

Please feel free to let me know if there’s anything I can help with. 

Looking forward to hearing from you! 

Answer 2

Hello @Salah Ramadan,  

Thank you for reaching out to the Q&A forum! 

Based on my research, this issue is due to a limitation in Excel's Data Validation: it does not support dynamic array formulas like XLOOKUP directly in the Source field of a data validation list. 

Excel cannot evaluate functions like XLOOKUP directly in the Source box, which is why you're seeing the error message. 

However, to support you better, please provide me more detail: 

1. Could you please provide a detailed screenshot of the error message? 

I’ll help you blur or hide any personal information if needed 

2. Are you using a work account (managed by your organization) or a personal account? 

3. Which version of Excel are you currently using? (e.g., Excel for Microsoft 365, Excel 2021, etc.)
4. Would you be able to share a sample of your Excel file via private message?

I've sent you a private message to help protect your personal information. Please take a moment to check it out. 

Note: To access private message platform, please reload this thread page and select "View messages" as below screenshot.

Please understand that our initial response does not always resolve the issue immediately. However, with your help and more detailed information, we can work together to find a solution. 

Look forward to hearing from you!

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment". 

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread. 

Answer 3

Hello, many thanks for reverting back and for your support, please find attached the error message.