VBA Vlookup between two workbooks

mayaa_mmm

Board Regular
Joined
Jul 30, 2014
Messages
54
Office Version
  1. 2010
Platform
  1. 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
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
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
 
Upvote 0
=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
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

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