How do I pull data from one sheet into another based on matching criteria?

[lwwke]

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.

[#2]

I think you need to use an xlookup function. I could help if you share an exemple of data with the expected result

[#3]

@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

[#4]

@lwwke 

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

[#5 · Accepted solution]

@lwwke 

=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.

[#6]

Thanks a lot, @OliverScheurich! That did the trick. Appreciate your help as well, @durendal