Partial matching data

[Andrew Watson]

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.

 

[Phuoc]

Try this:

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

 

[Zot]

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
	A	B	C	D	E	F	G	H	I	J
1	FX Job			FX Rate			Master Job			FX Rate
2	N11145			0.61067			31N11145			0.61067
3	N11152			1.37736			31N11152			1.37736
4	N11146			0.203054			31N11146			0.203054
Sheet1

Cell Formulas
Range	Formula
J2:J4	J2	=INDEX($A$2:$G$4,MATCH("*"&A2,$G$2:$G$4,0),4)

 

[Fluff]

If it's always the last 6 characters you want to lookup

=INDEX($D$2:$D$4,MATCH(RIGHT(G2,6),$A$2:$A$4,0))

 

[Andrew Watson]

If it's always the last 6 characters you want to lookup

=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]

Glad we could help & thanks for the feedback.