SOLVED

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

Copper Contributor

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.

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

@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 IDClient NameActive?Assigned toIntake Date
000001JamesYesRoger1/1/20
000002RobertYesSylvio1/1/20
000003LisaYesDina1/1/20
000004SaraNoDina1/1/20
000005QuinnYesSylvio1/1/20
000006TaylerYesRoger1/1/20
000007JoseYesRoger1/1/20
000008FeliceYesSylvio1/1/20
000009CarmenYesDina1/1/20
000010BarryNoDina1/1/20
000011JuneYesSylvio1/1/20
000012SouaYesRoger1/1/20
000013XavierYesRoger1/1/20
000014KristaYesDina1/1/20
000015JessNoSylvio1/1/20

 

SHEET 2:

Client NameAmount DueAssigned 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 

@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

best response confirmed by lwwke (Copper Contributor)
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.

pull data.JPG

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

Accepted Solutions
best response confirmed by lwwke (Copper Contributor)
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.

pull data.JPG

View solution in original post