More sophisticated VLOOKUP

[SunKing951]

Good evening!

Does anyone know how to do a VLOOKUP formula which, instead of searching for an exact match, instead looks for only a certain amount of the first characters of the searched field to match, perhaps the first 10 for example?

Please let me know if I'm not being clear.

Thanks

 

[Jonmo1]

Taking a standard vlookup as example...
=VLOOKUP(A1,B:C,2,FALSE)

To make it look for A1 in Column B.
If A1 = Jon, then
Column B could be Jon******
If A1 = Hello, then
Column B could be Hello*****

=INDEX(C:C,MATCH(A1&"*",B:B,0))


Hope that helps.

 

[SunKing951]

Taking a standard vlookup as example...
=VLOOKUP(A1,B:C,2,FALSE)

To make it look for A1 in Column B.
If A1 = Jon, then
Column B could be Jon******
If A1 = Hello, then
Column B could be Hello*****

=INDEX(C:C,MATCH(A1&"*",B:B,0))


Hope that helps.

Thanks. How would it work though if it weren't just a case of A1 being a shorter version and column B being that shorter version + undefined other characters? For example, what if A1 was sometimes "Jonathan", sometimes "Jonath21", or "Jonafun" etc.? In that case I'd want to make the formula reference only the first 4 characters of A1, and then only the first four of column B also. Any ideas?

Cheers

 

[Aladin Akyurek]

Thanks. How would it work though if it weren't just a case of A1 being a shorter version and column B being that shorter version + undefined other characters? For example, what if A1 was sometimes "Jonathan", sometimes "Jonath21", or "Jonafun" etc.? In that case I'd want to make the formula reference only the first 4 characters of A1, and then only the first four of column B also. Any ideas?

Cheers

Good evening!

Does anyone know how to do a VLOOKUP formula which, instead of searching for an exact match, instead looks for only a certain amount of the first characters of the searched field to match, perhaps the first 10 for example?

Please let me know if I'm not being clear.

Thanks

Have a look also at:

http://www.mrexcel.com/forum/showthread.php?t=195635

You might want to consider providing sample data with the expected results though.

 

[Jonmo1]

Try

=INDEX(C:C,MATCH(LEFT(A1,4)&"*",B:B,0))

 

[SunKing951]

Thanks, I'll try this