Vlookup against last character

[excel.vba]

Hello

Wanted to know if there is a formula which allows me to look up against the last character of a range. This is necessary especially when i am looking up against a template.

For example.
If i have a codeNo 100-A, and i need to lookup its definition. There are many codeNo like 101-A,102-A...
The range i need to check against would be xxx-A, since they all follow the same definition. I am not sure if wildcard can be used in the lookup range of the VLOOKUP function.
Currently i am using the following function
=VLOOKUP(("xxx"&RIGHT(A5,2)),Tempate!A:B,2,0)

However, there are times where the template could be 0xx-A. So i cant hardcode the first few characters.

any help would be greatly appreciated. Thanks!

 

[Peter_SSs]

Perhaps I havben't understood, but couldn't you use a standard VLOOKUP?

=VLOOKUP("101-A",Range_to_Lookup,Column_to_look_in,0)

 

[excel.vba]

The range i am looking up against is a template range so it will only have the following "xxx-A", "xxx-B","xxx-C", etc. Btw xxx doesnt stand for numbers. It is literally "xxx". So i need to match the last letter of the range i am using to lookup against the last letter of the template range. Hope this clears things up abit

 

[Aladin Akyurek]

Hello

Wanted to know if there is a formula which allows me to look up against the last character of a range. This is necessary especially when i am looking up against a template.

For example.
If i have a codeNo 100-A, and i need to lookup its definition. There are many codeNo like 101-A,102-A...
The range i need to check against would be xxx-A, since they all follow the same definition. I am not sure if wildcard can be used in the lookup range of the VLOOKUP function.
Currently i am using the following function
=VLOOKUP(("xxx"&RIGHT(A5,2)),Tempate!A:B,2,0)

However, there are times where the template could be 0xx-A. So i cant hardcode the first few characters.

any help would be greatly appreciated. Thanks!

Does one of...

=VLOOKUP("*"&RIGHT(A5),Tempate!A:B,2,0)

=VLOOKUP("*"&RIGHT(A5,2),Tempate!A:B,2,0)

give what you want?

 

[excel.vba]

I had already gotten around this problem. But this works awesome as well. Will just use this instead. Thank you for your time and solutions.

 

[Aladin Akyurek]

I had already gotten around this problem. But this works awesome as well. Will just use this instead. Thank you for your time and solutions.

You are welcome. Thanks for providing us feedback.