Case-sensitive VLOOKUP for Salesforce Record Id

Salesforce record Id will always return 15 characters across all objects, and the Id is case sensitive. Of course, if you have admin access, you can create a formula field CASESAFEID(Id) and this will return 18 characters which are case-insensitive.

But, if you have to work with case-sensitive 15 characters and do VLOOKUP in Excel, you need to do extra work, as you probably know that one of the limitations of VLOOKUP is case-insensitive, it means the case is not a consideration in the lookup process, example: 0038000001c9YsM is essentially the same as 0038000001c9Ysm. Hence, VLOOKUP will consider that they are the same and return the first matched, which is totally wrong.

There are a few options you can do for VLOOKUP:

1. Convert 15 to 18 characters
We have shared this in a blog Convert ID from 15 to 18 characters with Excel formula, so you need to have an additional column in the source and lookup data, and use that column for VLOOKUP.

2. Convert to ASCII
This is pretty similar to option 1 above, instead of converting 15 characters case-sensitive to 18 characters case-insensitive, we convert the record Id into ASCII using CODE(), Microsoft Excel CODE function returns the ASCII value of a character or the first character in a cell.

=CODE(MID(A2,1,1)) & CODE(MID(A2,2,1)) & CODE(MID(A2,3,1)) & CODE(MID(A2,4,1)) & CODE(MID(A2,5,1)) & CODE(MID(A2,6,1)) & CODE(MID(A2,7,1)) & CODE(MID(A2,8,1)) & CODE(MID(A2,9,1)) & CODE(MID(A2,10,1)) & CODE(MID(A2,11,1)) & CODE(MID(A2,12,1)) & CODE(MID(A2,13,1)) & CODE(MID(A2,14,1)) & CODE(MID(A2,15,1))

You need to convert both source and column data Record Id to ASCII, then use it for VLOOKUP().


Reference:

• Convert ID from 15 to 18 characters with Excel formula
• 4 ways to do a case-sensitive vlookup in Excel
• Convert Salesforce Ids from 15 to 18 characters