Partial matching data

Andrew Watson

New Member
Joined
Feb 3, 2016
Messages
10
Office Version
  1. 365
Platform
  1. Windows
Hi everyone,

I have a dilemma whereby I'm trying to populate a column of data from tab to another using a partial match for the data. The issue is I have to start with the whole number, in this case 31N11145 and then find an FX Currency Rate on another tab of the same workbook but the cell only contains N11145.

Using the instance below my "Master Job" in Column G is on the Master Tab and I need to add in the FX Rate in column J of the same tab. The "FX Job" column A and "FX Rate" column D appears on the FX data tab. I'm trying to populate column J by trying to partial match the data in column A using the full data in column G. If I were to swap columns A & J everything works fine but I can't do that, nor can I simply add 31 to the start of column A as there is other data in there that cannot be changed as it already has a 31 prefix for other jobs. I've searched and searched multiple posts but turn up with nothing. Any help is most appreciated.

Annotation.png


Thanks Andrew.
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,176
Office Version
  1. 2016
Platform
  1. Windows
For the VLOOKUP to work, the search column has to be on the left, meaning the column G has to be in column A position. Now you are looking the partial match of data in column A to column A it self.

Alternative is to use MATCH and INDEX

CSV sheet.xlsx
ABCDEFGHIJ
1FX JobFX RateMaster JobFX Rate
2N111450.6106731N111450.61067
3N111521.3773631N111521.37736
4N111460.20305431N111460.203054
Sheet1
Cell Formulas
RangeFormula
J2:J4J2=INDEX($A$2:$G$4,MATCH("*"&A2,$G$2:$G$4,0),4)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,856
Office Version
  1. 365
Platform
  1. Windows
If it's always the last 6 characters you want to lookup
Excel Formula:
=INDEX($D$2:$D$4,MATCH(RIGHT(G2,6),$A$2:$A$4,0))
 
Solution

Andrew Watson

New Member
Joined
Feb 3, 2016
Messages
10
Office Version
  1. 365
Platform
  1. Windows
If it's always the last 6 characters you want to lookup
Excel Formula:
=INDEX($D$2:$D$4,MATCH(RIGHT(G2,6),$A$2:$A$4,0))
Hi Fluff,

Your answer worked perfectly. Thank you very much.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,856
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,129
Messages
5,640,295
Members
417,135
Latest member
zeusmining

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
Top