VBA Vlookup between two workbooks

mayaa_mmm

Board Regular
Joined
Jul 30, 2014
Messages
54
Office Version
2010
Platform
Windows
I need to vlookup with two workbooks and find the match values with highlight.
Workbook A (Located in the folder path C:/Desktop/price match
MFRdate begdate endprice
XXX-ltd8/1/208/1/20100
YYY-ltd9/1/209/1/20200
GGG-ltd10/1/2010/1/20300
MMM-ltd11/1/2011/1/20400

Workbook B(Located in the folder path C:/Desktop/price match
MFRdate begdate endpricecheck(outuput column)
XXX8/1/208/1/20100Match
YYY9/1/208/1/20200Match

by matching all the columns in workbook A with workbook B, then check (output column) should say match. -ltd in mfr of column A in workbook A should be ignored while comparing with workbook B . Since workbook B column MFR will not have -ltd
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

braindiesel

Well-known Member
Joined
Mar 16, 2009
Messages
552
Office Version
365, 2019, 2010
Platform
Windows
VBA not required... you can do this with a helper column...
Book4
ABCDEFGHIJKLM
1MFRdate begdate endpriceMFRdate begdate endpricecheck(outuput column)
2XXX4404444044XXX-ltd8/1/20208/1/2020100XXX4404444044XXX8/1/20208/1/2020100Match
3YYY4407544075YYY-ltd9/1/20209/1/2020200YYY4407544044YYY9/1/20208/1/2020No MatchMatch
4GGG4410544105GGG-ltd10/1/202010/1/2020300
5MMM4413644136MMM-ltd11/1/202011/1/2020400
Sheet1
Cell Formulas
RangeFormula
H2:H3H2=I2&J2&K2
L2:L3L2=IFERROR(VLOOKUP(H2,$A$2:$E$5,5,FALSE),"No Match")
A2:A5A2=LEFT(B2,FIND("-",B2)-1)&C2&D2
 

mayaa_mmm

Board Regular
Joined
Jul 30, 2014
Messages
54
Office Version
2010
Platform
Windows
=IFERROR(VLOOKUP(H2,$A$2:$E$5,5,FALSE),"No Match"
Thank you for you quick update

but i getting # VALUE error for the formula
=LEFT(B2,FIND("-",B2)-1)&C2&D2
only first line worked

=LEFT(B3,FIND("-",B3)-1)&C3&D3 I m getting error as #value
 

braindiesel

Well-known Member
Joined
Mar 16, 2009
Messages
552
Office Version
365, 2019, 2010
Platform
Windows
Odd... that is the formula i used in that cell with your data...
Are you trying it on other data and there is no hyphen "-" in it?
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,179
Messages
5,509,643
Members
408,746
Latest member
Faker4442

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top