Apr 25 2023 08:09 AM
This seems simple, but I'm simple, too, so... Also, private data, so can't share my workbooks.
I have Sheet 1, with thousands of client names in one column and the assigned caseworker for each client in another column. And I have Sheet 2, which has hundreds of client names, most (but not all) of which can also be found in Sheet 1, but Sheet 2 does not list the assigned caseworker. I would like to populate Sheet 2 with the correct caseworkers for those clients also listed in Sheet 1 (otherwise it can just say N/A). Any help would be much appreciated.
Apr 25 2023 08:18 AM
Apr 25 2023 09:15 AM - edited Apr 25 2023 09:28 AM
@durendalHere are some slimmed down made up examples. Assume thousands of rows in sheet 1 and hundreds in sheet 2, with many more columns of data in each. But the only concern for now is getting "Assigned to" populated correctly in Sheet 2.
SHEET 1:
Client ID | Client Name | Active? | Assigned to | Intake Date |
000001 | James | Yes | Roger | 1/1/20 |
000002 | Robert | Yes | Sylvio | 1/1/20 |
000003 | Lisa | Yes | Dina | 1/1/20 |
000004 | Sara | No | Dina | 1/1/20 |
000005 | Quinn | Yes | Sylvio | 1/1/20 |
000006 | Tayler | Yes | Roger | 1/1/20 |
000007 | Jose | Yes | Roger | 1/1/20 |
000008 | Felice | Yes | Sylvio | 1/1/20 |
000009 | Carmen | Yes | Dina | 1/1/20 |
000010 | Barry | No | Dina | 1/1/20 |
000011 | June | Yes | Sylvio | 1/1/20 |
000012 | Soua | Yes | Roger | 1/1/20 |
000013 | Xavier | Yes | Roger | 1/1/20 |
000014 | Krista | Yes | Dina | 1/1/20 |
000015 | Jess | No | Sylvio | 1/1/20 |
SHEET 2:
Client Name | Amount Due | Assigned to |
James | $ 1,395.36 | |
Robert | $ 985.25 | |
Lisa | $ 57.23 | |
Quinn | $ - | |
Tayler | $ 6,521.14 | |
Felice | $ 844.85 | |
Barry | $ 99.64 | |
June | $ 115.47 | |
Xavier | $ 665.00 | |
Krista | $ - | |
Jess | $ 3.88 |
Apr 25 2023 09:30 AM - edited Apr 25 2023 09:30 AM
This xlookup formula should do the trick, however there is one major issue. You are using names, which might not be a unique identifier. If two persons are named Jesse for exemple, the sheet nb 2 will always give you the value of the first Jesse. I added a formula that shows you the duplicate values in your list.
Hope it helps
Apr 25 2023 09:36 AM
Solution=INDEX(Sheet1!$D$2:$D$16,MATCH(A2,Sheet1!$B$2:$B$16,0))
If you don't have access to XLOOKUP you can try this formula. The ranges can be adapted as required.
Apr 25 2023 10:35 AM