Forum Discussion

lwwke's avatar
lwwke
Copper Contributor
Apr 25, 2023
Solved

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

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 clien...
excel
Formulas and Functions
  • OliverScheurich's avatar
    OliverScheurich
    Apr 25, 2023

    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.

durendal's avatar
durendal
Brass Contributor
Apr 25, 2023
I think you need to use an xlookup function. I could help if you share an exemple of data with the expected result
lwwke's avatar
lwwke
Copper Contributor
Apr 25, 2023

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 
  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Apr 25, 2023

    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.

    • lwwke's avatar
      lwwke
      Copper Contributor
      Apr 25, 2023
      Thanks a lot, OliverScheurich! That did the trick. Appreciate your help as well, durendal
  • durendal's avatar
    durendal
    Brass Contributor
    Apr 25, 2023

    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

Resources