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

 

[KirstyFrance]

Hi and thanks for the replies,

I have tried the last post by MartinDWilson has worked which is great!

I have tried the following:
=LOOKUP(9.99E+307,FIND(A27,Sheet2!A:C),Sheet2!A1:A2000)

However I don't understand what the 9.99E+307 part of the formula is doing?

I prefer to understand how something works before using it, so if you are able to explain that would be great.


Thanks
Kirsty

 

[martindwilson]

its a very big number bigger than any result you'd get back so lookup finds the match that
is
If the LOOKUP function can't find the lookup_value, the function matches the largest value in lookup_vector that is less than or equal to lookup_value.
try evalauating the formula to see whats happening