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.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]NAME[/TD]
[TD]SORT CODE[/TD]
[TD]ACCOUNT NO.[/TD]
[TD]FULL LINE[/TD]
[TD]LOOKUP[/TD]
[/TR]
[TR]
[TD]C Cuthell[/TD]
[TD][TABLE="width: 76"]
<tbody>[TR]
[TD="class: xl65, width: 76"]97-46-27[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 96"]
<tbody>[TR]
[TD="class: xl65, width: 96"]59418670[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 425"]
<tbody>[TR]
[TD="class: xl65, width: 425"]c cuthell CHARLOTTES MONEY BGCFrom: 97-46-27 59418670[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 377"]
<tbody>[TR]
[TD="class: xl65, width: 377"]c cuthell CHARLOTTES MONEY BGCFrom: 97-46-27 59418670[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]VLOOKUP(A2&B2&C2,D:D,1,1)[/TD]
[/TR]
</tbody>[/TABLE]

 

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