Combine data from 2 worksheets if the text matches

K

kevgray73

New Member
Joined
Mar 23, 2022
Messages
4
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi All,
i have a spreadsheet with 3 sheets inside, sheet 1 is a full list of parts but its missing details in the last column (Zoll). Sheet 2 has the details required in the (Zoll) column but it isn't a full list. Sheet 3 is where i would like to have it look at the details in column (Benunnung) from sheet 2 and if the item matches then it would take the details from the Zoll column and fill in the details in sheet 3. Anywhere there isn't an item in sheet 2 that is in sheet 3 it would then leave the cell blank. I have attached 3 images just for reference (they are just partial screenshots but it shows the idea. i have coloured the column where the details is missing or where it comes from. the data is sheet 1 and 3 will always be the same but sheet 2 could have defferent amounts of items.

I hope this makes sense to someone?
TIA
Kev
 

Attachments

  • Sheet1.JPG
    Sheet1.JPG
    222.1 KB · Views: 5
  • sheet2.JPG
    sheet2.JPG
    227.4 KB · Views: 5
  • sheet3.JPG
    sheet3.JPG
    227.1 KB · Views: 4

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Sort by date Sort by votes
In Excel 365, give this a try to see if it produces the expected results. I will caution that making a match on BENENNUNG columns may be problematic, as every space and punctuation mark matters...as well as minor spelling errors. My table here may have some errors, as I converted your worksheet to text with optical character recognition:
MrExcel_20220505_KevGray.xlsx
ABCDEFGH
1POS-NR.BENENNUNGMENGEBeschreibung:PartNoLieferant:Gewicht:Zoll
240gerade Einschraubverschraubung - CK-1-2-12-10-MS1StandardCK-12-1210-MSBZO511
341gerade Einschraubverschraubung - CK-1-4-10-8-MS1StandardCK-14-108-MSBZO262
442gerade Einschraubverschraubung - CK-1-4-6-4-MS1StandardCK-14-64-MSBZO213
543gerade Einschraubverschraubung - CK-1-4-8-6-MS1StandardCK-14-86-MSBZO234
644gerade Einschraubverschraubung - CK-3-8-10-8-MS1StandardCK-38-108-MSBZO32not found
745gerade Einschraubverschraubung - CK-3-8-8-6-MS1StandardCK-38-86-MSBZO06
81Gewindetulle - N 1-4-91Standard0BZO0not found
92Kupplungsdose - KDK-4-51StandardKDK-4-5BZO34not found
103Kupplungsdose - KDK-6-71Standard0BZO0not found
116Kupplungsdose fur Gewebeschlauch - KDS-9-101StandardKDS-9-10BZO0not found
127Kupplungsdose fur Gewebeschlauch - KDS-9-71StandardKDS-9-7BZO68not found
138Kupplungsdose fur Gewebeschlauch KDS-9-71Standard0BZOnot found
144Kupplungsdose KDa-14-51StandardKDa-14-5BZO35not found
155Kupplungsdose KDa-38-71StandardKDa-38-7BZO0not found
169Kupplungsstecker fur PU Schlauch -KSK-4-51StandardKSK-4-5BZO14not found
1710Kupplungsstecker fur PU Schlauch -KSK-6-71StandardKSK-6-7BZO19not found
1811Kupplungsstecker mil AuBengewinde - KS-1-2-71StandardKS-12-7BZO0not found
1912Kupplungsstecker mil AuBengewinde - KS-14-71DefaultKS-14-7BZO21not found
2013Kupplungsstecker mil AuBengewinde - KS-3-4-101StandardKS-3/4-10BZO0not found
2114Kupplungsstecker mil AuBengewinde KS-3-8-71StandardKS-38-7BZOnot found
2215Kupplunsstecker KSS-9-71Standard0BZO14not found
2318Minikugelhahn mil Knebel kompakt PN 20-KH-I-4-Ki-a1StandardKH-14-K-iaBZO0not found
2416Minikugelhahn mil Knebel PN 15-KH-12-1i-a1StandardKH-12-1-iaBZO0not found
2517Minikugelhahn mil Knebel PN 15-KH-38-1 i-a1StandardKH-38-1-iaBZO65not found
2619Muffe - QM-12-12-MS1Standard0BZO010
2720Reduziernippel-D-14-121Standard0BZO0not found
2821Schlauchschelle , Stahl verzinkt-SS 161SS-16ZZ-16BZO0not found
2922Steckverschraubung - KSS 14-61StandardKSS-14-6BZO013
Sheet3
Cell Formulas
RangeFormula
H2:H29H2=XLOOKUP(B2,Sheet2!$B$2:$B$100,Sheet2!$H$2:$H$100,"not found",0)
 
Upvote 0
Solution
KRice said:
In Excel 365, give this a try to see if it produces the expected results. I will caution that making a match on BENENNUNG columns may be problematic, as every space and punctuation mark matters...as well as minor spelling errors. My table here may have some errors, as I converted your worksheet to text with optical character recognition:
MrExcel_20220505_KevGray.xlsx
ABCDEFGH
1POS-NR.BENENNUNGMENGEBeschreibung:PartNoLieferant:Gewicht:Zoll
240gerade Einschraubverschraubung - CK-1-2-12-10-MS1StandardCK-12-1210-MSBZO511
341gerade Einschraubverschraubung - CK-1-4-10-8-MS1StandardCK-14-108-MSBZO262
442gerade Einschraubverschraubung - CK-1-4-6-4-MS1StandardCK-14-64-MSBZO213
543gerade Einschraubverschraubung - CK-1-4-8-6-MS1StandardCK-14-86-MSBZO234
644gerade Einschraubverschraubung - CK-3-8-10-8-MS1StandardCK-38-108-MSBZO32not found
745gerade Einschraubverschraubung - CK-3-8-8-6-MS1StandardCK-38-86-MSBZO06
81Gewindetulle - N 1-4-91Standard0BZO0not found
92Kupplungsdose - KDK-4-51StandardKDK-4-5BZO34not found
103Kupplungsdose - KDK-6-71Standard0BZO0not found
116Kupplungsdose fur Gewebeschlauch - KDS-9-101StandardKDS-9-10BZO0not found
127Kupplungsdose fur Gewebeschlauch - KDS-9-71StandardKDS-9-7BZO68not found
138Kupplungsdose fur Gewebeschlauch KDS-9-71Standard0BZOnot found
144Kupplungsdose KDa-14-51StandardKDa-14-5BZO35not found
155Kupplungsdose KDa-38-71StandardKDa-38-7BZO0not found
169Kupplungsstecker fur PU Schlauch -KSK-4-51StandardKSK-4-5BZO14not found
1710Kupplungsstecker fur PU Schlauch -KSK-6-71StandardKSK-6-7BZO19not found
1811Kupplungsstecker mil AuBengewinde - KS-1-2-71StandardKS-12-7BZO0not found
1912Kupplungsstecker mil AuBengewinde - KS-14-71DefaultKS-14-7BZO21not found
2013Kupplungsstecker mil AuBengewinde - KS-3-4-101StandardKS-3/4-10BZO0not found
2114Kupplungsstecker mil AuBengewinde KS-3-8-71StandardKS-38-7BZOnot found
2215Kupplunsstecker KSS-9-71Standard0BZO14not found
2318Minikugelhahn mil Knebel kompakt PN 20-KH-I-4-Ki-a1StandardKH-14-K-iaBZO0not found
2416Minikugelhahn mil Knebel PN 15-KH-12-1i-a1StandardKH-12-1-iaBZO0not found
2517Minikugelhahn mil Knebel PN 15-KH-38-1 i-a1StandardKH-38-1-iaBZO65not found
2619Muffe - QM-12-12-MS1Standard0BZO010
2720Reduziernippel-D-14-121Standard0BZO0not found
2821Schlauchschelle , Stahl verzinkt-SS 161SS-16ZZ-16BZO0not found
2922Steckverschraubung - KSS 14-61StandardKSS-14-6BZO013
Sheet3
Cell Formulas
RangeFormula
H2:H29H2=XLOOKUP(B2,Sheet2!$B$2:$B$100,Sheet2!$H$2:$H$100,"not found",0)
Click to expand...

KRice said:
In Excel 365, give this a try to see if it produces the expected results. I will caution that making a match on BENENNUNG columns may be problematic, as every space and punctuation mark matters...as well as minor spelling errors. My table here may have some errors, as I converted your worksheet to text with optical character recognition:
MrExcel_20220505_KevGray.xlsx
ABCDEFGH
1POS-NR.BENENNUNGMENGEBeschreibung:PartNoLieferant:Gewicht:Zoll
240gerade Einschraubverschraubung - CK-1-2-12-10-MS1StandardCK-12-1210-MSBZO511
341gerade Einschraubverschraubung - CK-1-4-10-8-MS1StandardCK-14-108-MSBZO262
442gerade Einschraubverschraubung - CK-1-4-6-4-MS1StandardCK-14-64-MSBZO213
543gerade Einschraubverschraubung - CK-1-4-8-6-MS1StandardCK-14-86-MSBZO234
644gerade Einschraubverschraubung - CK-3-8-10-8-MS1StandardCK-38-108-MSBZO32not found
745gerade Einschraubverschraubung - CK-3-8-8-6-MS1StandardCK-38-86-MSBZO06
81Gewindetulle - N 1-4-91Standard0BZO0not found
92Kupplungsdose - KDK-4-51StandardKDK-4-5BZO34not found
103Kupplungsdose - KDK-6-71Standard0BZO0not found
116Kupplungsdose fur Gewebeschlauch - KDS-9-101StandardKDS-9-10BZO0not found
127Kupplungsdose fur Gewebeschlauch - KDS-9-71StandardKDS-9-7BZO68not found
138Kupplungsdose fur Gewebeschlauch KDS-9-71Standard0BZOnot found
144Kupplungsdose KDa-14-51StandardKDa-14-5BZO35not found
155Kupplungsdose KDa-38-71StandardKDa-38-7BZO0not found
169Kupplungsstecker fur PU Schlauch -KSK-4-51StandardKSK-4-5BZO14not found
1710Kupplungsstecker fur PU Schlauch -KSK-6-71StandardKSK-6-7BZO19not found
1811Kupplungsstecker mil AuBengewinde - KS-1-2-71StandardKS-12-7BZO0not found
1912Kupplungsstecker mil AuBengewinde - KS-14-71DefaultKS-14-7BZO21not found
2013Kupplungsstecker mil AuBengewinde - KS-3-4-101StandardKS-3/4-10BZO0not found
2114Kupplungsstecker mil AuBengewinde KS-3-8-71StandardKS-38-7BZOnot found
2215Kupplunsstecker KSS-9-71Standard0BZO14not found
2318Minikugelhahn mil Knebel kompakt PN 20-KH-I-4-Ki-a1StandardKH-14-K-iaBZO0not found
2416Minikugelhahn mil Knebel PN 15-KH-12-1i-a1StandardKH-12-1-iaBZO0not found
2517Minikugelhahn mil Knebel PN 15-KH-38-1 i-a1StandardKH-38-1-iaBZO65not found
2619Muffe - QM-12-12-MS1Standard0BZO010
2720Reduziernippel-D-14-121Standard0BZO0not found
2821Schlauchschelle , Stahl verzinkt-SS 161SS-16ZZ-16BZO0not found
2922Steckverschraubung - KSS 14-61StandardKSS-14-6BZO013
Sheet3
Cell Formulas
RangeFormula
H2:H29H2=XLOOKUP(B2,Sheet2!$B$2:$B$100,Sheet2!$H$2:$H$100,"not found",0)
Click to expand...

Thanks for your quick reply. I tried it but it doesnt recognise the function XLOOKUP. Is this only in 365 versions?
Thanks
 
Upvote 0
Ok, for older version you can use
Excel Formula: 
=IFNA(INDEX(Sheet2!$H$2:$H$100,MATCH(B2,Sheet2!$B$2:$B$100,0)),"not found")
 
Upvote 0
Glad we could help & thanks for the feedback.
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Solved
Populating combobox from table based on the header selected in another combobox (some data on different sheets)
Replies
7
Views
228
BWL388
B
D
  • Question
Assistance with VLookup
Replies
4
Views
202
etaf
etaf
A
  • Question
Xlookup, Vlookup and/or VBA for finding, matching and inputting data to separate sheet
Replies
0
Views
516
AlfredDB
A
K
  • Question
Formula needed to look up specific name and enter data from specific column within same row
Replies
1
Views
183
awoohaw
awoohaw
D
  • Solved
Assistance with VLookup Formula
Replies
4
Views
273
I_know_nuffin
I

Forum statistics

Threads
1,214,646
Messages
6,120,715
Members
448,985
Latest member
chocbudda

Share this page

We've detected that you are using an adblocker.

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.
Go back
Back
Top