Looking up an ID number of letters and numbers - exact match

[KirstyFrance]

Hi,

I'm having difficulty doing Vlookups on ID numbers from an external system because they contain letters and numbers of lower and upper case. As this system distinguishes between lower and upper case there are numbers that are have the same combination of letters and numbers but are a different ID because one is lower and one is upper.

Neither the MATCH or VLOOKUP formulas seem to handle this.

Any ideas?

Many thanks
Kirsty

 

[Sportman1975]

Send me the file


E-mail address removed

 

[jasonb75]

Welcome to the board!

You could do it with an array formula on the principle of

=MATCH(TRUE,IF(ISNUMBER(FIND(lookup value,lookup range)),LEN(lookup value)=LEN(lookup range)),0)

Note that after you edit your ranges into the formula you will need to confirm it with Shift Ctrl Enter.

 

[pgc01]

Hi Kristy
Welcome to the board

You don't give enough information.

It's important to always describe the problem and post a working example with inputs, logic and expected results. This helps everyone to understand the problem and makes sure the answers really solve the problem.

In this case, please post some IDs and examples of the patterns you use to look them up.

Please check Posting rule 13:

Provide background information for your question. Include sample data and formulas in your post. The more you tell us up front, the easier it is for us to answer your questions. If your formulas aren't giving the results you want, include the results you want as well as the results you are currently getting. Your post should include at least:
some sample data
your current formula
your current results
an explanation of why your current results are not what you want
the result you want

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

 

[pgc01]

Hi Sportman1975

Please don't ask for information to be sent away from the board.

The purpose of the board is to share information. A clear post, with all the information necessary available, ensures that everyone can contribute, detect errors, improve solutions, use the information for similar problems, etc., and this is what this board is about.

This is similar to the cases of posting rules 18 and 19:

18 - Do not e-mail or private message your questions directly to board members. Many people here could have the answer to your question and by posting you may also be helping others who are having the same problem. If you post to the board you'll likely have an answer within a few hours (sometimes even within a few minutes!).


19 - Do not post your question and request that you be e-mailed directly. It defeats the purpose of the board entirely (see above point). Openly posted e-mail addresses are regularly reported and removed by a moderator or administrator. Also, SPAMMERS routinely search and harvest e-mail addresses posted on public forums such as this one. So posting an openly unedited e-mail address on this forum can lead to an increase in the amount of SPAM sent to that e-mail address.

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

 

[martindwilson]

maybe
=LOOKUP(9.99E+307,FIND(B1,D1:D10),F1:F10)

Excel Workbook
	A	B	C	D	E	F
1	2	GH34H		gh34h		1
2				GH34H		2
3				123A		3
4				123a		4
Sheet3

 

[martindwilson]

also assuming they are unique in string else it would find 1GH34H,GH34H,wGH34H23 and so on

 

[KirstyFrance]

Thanks for all the replies and sorry for not giving enough information.

The ID's look have this format:

00Q20000006CfyC
00Q20000006CfYC

These are two different IDs although the only difference is lower case y on the first versus upper case on the second.

If I use an normal vlookup, it will always return one of the two, but not always the correct one.

Using MATCH also does not distinguish between upper and lower case.

So I end up with something like this:

00Q20000006CfyC 00Q20000006CfYC
00Q20000006CfYC 00Q20000006CfYC

Regards

 

[jasonb75]

also assuming they are unique in string else it would find 1GH34H,GH34H,wGH34H23 and so on

How about

=LOOKUP(TRUE,EXACT(B1,D1:D4),F1:F10)

 

[martindwilson]

since they are all the same length then what i posted should work just fine

Excel Workbook
	A	B	C	D	E	F
1	2	00Q20000006CfYC		00Q20000006CfyC		1
2				00Q20000006CfYC		2
3
Sheet1