Match columns A & partial match column B in two different sheets and return 3rd column

M

mljohn

Board Regular
Joined
Aug 26, 2004
Messages
196
Office Version
  1. 365
Platform
  1. Windows
I have two sheets
Sheet1 and Sheet2 Column A have matchable data

Sheet1 and sheet2 column B have strings that don't exactly match
Example:
Col A---TN360,TN330
Col B---TN360
or
Col A---TN360
Col B---TN360,TN330
or
Col A---TN360
Col B---TN360

I want the result in column T to be from Column E in sheet2 if column A in sheet1 and sheet2 match and if Column A in sheet1 and sheet2 have a partial match in sheet1 and sheet2.
Some of my suppliers add more part numbers than others for each item. As long as two of the part numbers in Column B match I want the result of column E from sheet2

Can someone help?

Thanks

Sheet1
TestImageFormula.xlsx
ABCDEFGHIJ
1VendorBrandOEMOEM PlusPrimary KeyCostVendor#Large Image URLType descriptionColor
2IPWBrotherTN360,TN330$28.00TRUE
3IPWDell3302650, 3302649$48.50FALSE
4IPWDell34015HA, 34035HA$29.00FALSE
5CloverHPCE250X(J)$101.00TRUE
6LibertydellP7RMX, 593BBKD, CVXGF$149.89
AllVendor
Cell Formulas
RangeFormula
I2:I4I2=ISNUMBER(MATCH("*"&LEFT(C2,5)&"*",Images!B:B,0))



Sheet2
TestImageFormula.xlsx
ABCDEFG
1VendorOEMPrimary KeyOEM BrandLarge ImageAll Large ImageSmall Image
2IPWTN336YILG_TN336Y BROTHERSaltlakecitytoner.com/image/ILG/TN-336Y.jpgSaltlakecitytoner.com/image/ILG/TN-336Y.jpg
3IPWTN350ILG_TN350 BROTHERSaltlakecitytoner.com/image/ILG/TN-350.jpgSaltlakecitytoner.com/image/ILG/TN-350.jpg
4IPWTN360ILG_TN360 BROTHERSaltlakecitytoner.com/image/ILG/TN-360.jpgSaltlakecitytoner.com/image/ILG/TN-360.jpg
5IPWTN450ILG_TN450 BROTHERSaltlakecitytoner.com/image/ILG/TN-450.jpgSaltlakecitytoner.com/image/ILG/TN-450.jpg
6IPWTN460ILG_TN460 BROTHERSaltlakecitytoner.com/image/ILG/TN-460.jpgSaltlakecitytoner.com/image/ILG/TN-460.jpg
7CloverCE505A, 3479B001AAClover_CE505A, 3479B001AAHPhttp://www.cloverimaging.com/image/800/600/MSE02210514.jpghttp://www.cloverimaging.com/image/800/600/MSE02210514.jpg
8LibertyP7RMX, PVTHG, 593BBKD, CVXGF, 2RMPMDellhttp://libertylaserimages.com/Cartridge-Images-by-Brand-Full-Size/Dell-Mono/LLSE310.jpghttp://libertylaserimages.com/Cartridge-Images-by-Brand-Full-Size/Dell-Mono/LLSE310.jpghttp://libertylaserimages.com/Medium-Images/LLSE310.jpg
Images
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Sort by date Sort by votes
Try this:
Book1
ABCDEFG
1VendorOEMPrimary KeyOEM BrandLarge ImageAll Large ImageSmall Image
2IPWTN336YILG_TN336Y BROTHERSaltlakecitytoner.com/image/ILG/TN-336Y.jpgSaltlakecitytoner.com/image/ILG/TN-336Y.jpg
3IPWTN350ILG_TN350 BROTHERSaltlakecitytoner.com/image/ILG/TN-350.jpgSaltlakecitytoner.com/image/ILG/TN-350.jpg
4IPWTN360ILG_TN360 BROTHERSaltlakecitytoner.com/image/ILG/TN-360.jpgSaltlakecitytoner.com/image/ILG/TN-360.jpg
5IPWTN450ILG_TN450 BROTHERSaltlakecitytoner.com/image/ILG/TN-450.jpgSaltlakecitytoner.com/image/ILG/TN-450.jpg
6IPWTN460ILG_TN460 BROTHERSaltlakecitytoner.com/image/ILG/TN-460.jpgSaltlakecitytoner.com/image/ILG/TN-460.jpg
7CloverCE505A, 3479B001AAClover_CE505A, 3479B001AAHPhttp://www.cloverimaging.com/image/800/600/MSE02210514.jpghttp://www.cloverimaging.com/image/800/600/MSE02210514.jpg
8LibertyP7RMX, PVTHG, 593BBKD, CVXGF, 2RMPMDellhttp://libertylaserimages.com/Cartridge-Images-by-Brand-Full-Size/Dell-Mono/LLSE310.jpghttp://libertylaserimages.com/Cartridge-Images-by-Brand-Full-Size/Dell-Mono/LLSE310.jpghttp://libertylaserimages.com/Medium-Images/LLSE310.jpg
9
10
11
12Write At Column T
13#N/A
14#N/A
15Saltlakecitytoner.com/image/ILG/TN-336Y.jpg
16#N/A
17#N/A
18#N/A
19#N/A
20
images
Cell Formulas
RangeFormula
A7:A8A7=IF(COUNTIF(E7,"*ILG*"),"Misc", IF(COUNTIF(E7,"*Cloverimaging*"),"Clover",IF(COUNTIF(E7,"*Libertylaserimages*"),"Liberty","")))
F2:F5F2=(IF(E2<>"",E2, IF(OR(COUNTIF(C2,"*(M)*"),COUNTIF(C2,"*(J)*")), IF(ISNUMBER(SEARCH(C1,C2)),E1,IF(ISNUMBER(SEARCH(#REF!,C2)),#REF!,IF(ISNUMBER(SEARCH(C3,C2)),E3,""))),"")))
F6F6=(IF(E6<>"",E6, IF(OR(COUNTIF(C6,"*(M)*"),COUNTIF(C6,"*(J)*")), IF(ISNUMBER(SEARCH(C5,C6)),E5,IF(ISNUMBER(SEARCH(#REF!,C6)),#REF!,IF(ISNUMBER(SEARCH(#REF!,C6)),#REF!,""))),"")))
F7F7=(IF(E7<>"",E7, IF(OR(COUNTIF(C7,"*(M)*"),COUNTIF(C7,"*(J)*")), IF(ISNUMBER(SEARCH(#REF!,C7)),#REF!,IF(ISNUMBER(SEARCH(#REF!,C7)),#REF!,IF(ISNUMBER(SEARCH(C8,C7)),E8,""))),"")))
F8F8=(IF(E8<>"",E8, IF(OR(COUNTIF(C8,"*(M)*"),COUNTIF(C8,"*(J)*")), IF(ISNUMBER(SEARCH(C7,C8)),E7,IF(ISNUMBER(SEARCH(C6,C8)),E6,IF(ISNUMBER(SEARCH(C9,C8)),E9,""))),"")))
E13:E19E13=LOOKUP(2,1/((AllVendor!$A$2:$A$6=A2)*(SEARCH(B2,AllVendor!$C$2:$C$6))),images!$E$2:$E$8)
 
Upvote 0
maabadi said:
Try this:
Book1
ABCDEFG
1VendorOEMPrimary KeyOEM BrandLarge ImageAll Large ImageSmall Image
2IPWTN336YILG_TN336Y BROTHERSaltlakecitytoner.com/image/ILG/TN-336Y.jpgSaltlakecitytoner.com/image/ILG/TN-336Y.jpg
3IPWTN350ILG_TN350 BROTHERSaltlakecitytoner.com/image/ILG/TN-350.jpgSaltlakecitytoner.com/image/ILG/TN-350.jpg
4IPWTN360ILG_TN360 BROTHERSaltlakecitytoner.com/image/ILG/TN-360.jpgSaltlakecitytoner.com/image/ILG/TN-360.jpg
5IPWTN450ILG_TN450 BROTHERSaltlakecitytoner.com/image/ILG/TN-450.jpgSaltlakecitytoner.com/image/ILG/TN-450.jpg
6IPWTN460ILG_TN460 BROTHERSaltlakecitytoner.com/image/ILG/TN-460.jpgSaltlakecitytoner.com/image/ILG/TN-460.jpg
7CloverCE505A, 3479B001AAClover_CE505A, 3479B001AAHPhttp://www.cloverimaging.com/image/800/600/MSE02210514.jpghttp://www.cloverimaging.com/image/800/600/MSE02210514.jpg
8LibertyP7RMX, PVTHG, 593BBKD, CVXGF, 2RMPMDellhttp://libertylaserimages.com/Cartridge-Images-by-Brand-Full-Size/Dell-Mono/LLSE310.jpghttp://libertylaserimages.com/Cartridge-Images-by-Brand-Full-Size/Dell-Mono/LLSE310.jpghttp://libertylaserimages.com/Medium-Images/LLSE310.jpg
9
10
11
12Write At Column T
13#N/A
14#N/A
15Saltlakecitytoner.com/image/ILG/TN-336Y.jpg
16#N/A
17#N/A
18#N/A
19#N/A
20
images
Cell Formulas
RangeFormula
A7:A8A7=IF(COUNTIF(E7,"*ILG*"),"Misc", IF(COUNTIF(E7,"*Cloverimaging*"),"Clover",IF(COUNTIF(E7,"*Libertylaserimages*"),"Liberty","")))
F2:F5F2=(IF(E2<>"",E2, IF(OR(COUNTIF(C2,"*(M)*"),COUNTIF(C2,"*(J)*")), IF(ISNUMBER(SEARCH(C1,C2)),E1,IF(ISNUMBER(SEARCH(#REF!,C2)),#REF!,IF(ISNUMBER(SEARCH(C3,C2)),E3,""))),"")))
F6F6=(IF(E6<>"",E6, IF(OR(COUNTIF(C6,"*(M)*"),COUNTIF(C6,"*(J)*")), IF(ISNUMBER(SEARCH(C5,C6)),E5,IF(ISNUMBER(SEARCH(#REF!,C6)),#REF!,IF(ISNUMBER(SEARCH(#REF!,C6)),#REF!,""))),"")))
F7F7=(IF(E7<>"",E7, IF(OR(COUNTIF(C7,"*(M)*"),COUNTIF(C7,"*(J)*")), IF(ISNUMBER(SEARCH(#REF!,C7)),#REF!,IF(ISNUMBER(SEARCH(#REF!,C7)),#REF!,IF(ISNUMBER(SEARCH(C8,C7)),E8,""))),"")))
F8F8=(IF(E8<>"",E8, IF(OR(COUNTIF(C8,"*(M)*"),COUNTIF(C8,"*(J)*")), IF(ISNUMBER(SEARCH(C7,C8)),E7,IF(ISNUMBER(SEARCH(C6,C8)),E6,IF(ISNUMBER(SEARCH(C9,C8)),E9,""))),"")))
E13:E19E13=LOOKUP(2,1/((AllVendor!$A$2:$A$6=A2)*(SEARCH(B2,AllVendor!$C$2:$C$6))),images!$E$2:$E$8)
Click to expand...

I'm just getting #N/A when I paste =LOOKUP(2,1/((AllVendor!$A$2:$A$6=A2)*(SEARCH(B2,AllVendor!$C$2:$C$6))),images!$E$2:$E$8) into Allvendor H2
 
Upvote 0
When Formula Nothing Found , Show #N/A Error. Are you Sure You have Cell B2 Value at other sheet.
If you want to don't see #N/A Error then Use IFNA Formula.
Excel Formula: 
=IFNA(LOOKUP(2,1/((AllVendor!$A$2:$A$6=A2)*(SEARCH(B2,AllVendor!$C$2:$C$6))),images!$E$2:$E$8),"")
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
Matching two columns to two columns in different sheets and returning a third column result
Replies
5
Views
549
corinaas
C
M
  • Locked
  • Question Question
VBA : matching particular columns in two different sheets and return the result in another sheet
Replies
1
Views
570
Fluff
Fluff
V
  • Question Question
Compare two column values in different sheet and populate Sheet1 Column A values in Sheet2
Replies
2
Views
791
Vinothkumar
V
R
  • Question Question
extract data from different worksheets and summarises match data on another sheet format
Replies
0
Views
533
RAM1972
R
S
  • Question Question
Help needed to get values from different sheet and sum if the value in one column matches
Replies
1
Views
363
szpt9m
S

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

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