Vlookup for "not exact lookup value"

[Kashif Mehmood]

Hello everyone,

I have 3 different columns containing Name, sort code and account No. as "C cuthell", "974627", "59418670" , i want to combine these and match with a column from my bank statement which is "c cuthell CHARLOTTES MONEY BGCFrom: 97-46-27 59418670" and ultimately i want to use vlookup on that data.

Thanks.

 

[psulions83]

If the three values you gave are in A,B, C then the full statement line is in D, a vlookup using a concatenate and a closest match works. As for it working in a bigger set of data I am not sure as this match will find the first closest match. Try this.

A2, B2 and C2 would be the Name, Sort Code and Account No. The second 1 in the vlookup is the same as putting TRUE.

=VLOOKUP(A2&B2&C2,D:D,1,1)

 

[Kashif Mehmood]

Thankyou for your answer but its not working , Plus in the bank statement the sort code is also seperated by "-" after every 2 digits.

 

[psulions83]

I don't think the dashes are a problem as you are looking up that exact value. This is how my spreadsheet looked below.

NAME	SORT CODE	ACCOUNT NO.	FULL LINE	LOOKUP
C Cuthell	97-46-27 <tbody> </tbody>	59418670 <tbody> </tbody>	c cuthell CHARLOTTES MONEY BGCFrom: 97-46-27 59418670 <tbody> </tbody>	c cuthell CHARLOTTES MONEY BGCFrom: 97-46-27 59418670 <tbody> </tbody>
				VLOOKUP(A2&B2&C2,D:D,1,1)

<tbody>
</tbody>

 

[Kashif Mehmood]

Not working sir, giving me the wrong answer.. plus my data is comprised of thousand of rows, Approximate match is not working.

 

[RoryA]

Based on what you've posted, and assuming that the sort code column is stored as a number, you could use

A2&"*"&TEXT(B2,"00-00-00")&" "&C2

as the first argument in your VLOOKUP formula, making sure to use FALSE as the fourth argument.