# Partial matching data

#### Andrew Watson

##### New Member
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.

Thanks Andrew.

### Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

#### Phuoc

##### Active Member
Try this:

=LOOKUP(1,-SEARCH(\$A\$2:\$A\$11,G1),\$D\$2:\$D\$11)

#### Zot

##### Well-known Member
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
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))``

#### Andrew Watson

##### New Member
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,

#### Fluff

##### MrExcel MVP, Moderator
Glad we could help & thanks for the feedback.

Replies
0
Views
51
Replies
2
Views
40
Replies
3
Views
291
Replies
3
Views
127
Replies
1
Views
263

1,130,322
Messages
5,641,529
Members
417,214
Latest member
qweb

### 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.

### Which adblocker are you using?

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

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