I have a wide range of employees data, I have been using normal Xlookup to get the Person Number by looking up the Employee name. However I came across case similar to the example below, there is a two idfferent employees with two different Person Number, but have the same exact name. So, I was looking for a way to return all the results in drop down list and I can choose the correct one from it.
I tried to put the xlookup into a data validation, but it returned the first result only as shown in the below example.
|Any help of getting all the results in the list?
Thanks in advance.
For the future I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.
Could you use something like this?
Use some helper columns. I have used D, E, ... but they can be any columns and they could be hidden once you enter the formula shown in D16 and copied down as far as you might ever need.
Then set up the Data validation as shown.
Actually, this would be more compact and "neater". Rather than copying down all the rows, just set up this top-right section and the altered DV as shown.
We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel
Which adblocker are you using?
Disable AdBlock
Follow these easy steps to disable AdBlock
1)Click on the icon in the browser’s toolbar. 2)Click on the icon in the browser’s toolbar. 2)Click on the "Pause on this site" option.
Go back
Disable AdBlock Plus
Follow these easy steps to disable AdBlock Plus
1)Click on the icon in the browser’s toolbar. 2)Click on the toggle to disable it for "mrexcel.com".
Go back
Disable uBlock Origin
Follow these easy steps to disable uBlock Origin
1)Click on the icon in the browser’s toolbar. 2)Click on the "Power" button. 3)Click on the "Refresh" button.
Go back
Disable uBlock
Follow these easy steps to disable uBlock
1)Click on the icon in the browser’s toolbar. 2)Click on the "Power" button. 3)Click on the "Refresh" button.