How to get Excel Vlookup formula to recognise lower and uppercase.

[JennySommet]

Hello,

I need the formula below to recognize upper and lower text. For example column A contains Product ID a030900000OsXao. This value is being looked up in Lookup!J:L. The problem is that this ID is being treated the same as a030900000OsXAo even though they are different IDs, because the letter 'a' is being treated same as 'A' in second ID.

The formula below in column S of my excel attached needs updating to recognize upper and lower text.

=IFERROR(VLOOKUP(A2,Lookup!J:L,2,FALSE),B2)

Help is appreciated!

Thanks

Jenny

[#2]

@JennySommet 

This is the idea:

=LET(names,$E$2:$E$10,return,$F$2:$F$10,r,MATCH(TRUE,EXACT(I2,names),0),INDEX(return,r))

[#3]

Thanks for you help, I am not sure how to incorporate into my excel, so I will send an attachment in response to this post.

[#4]

@JennySommet 

Here's the updated formula:

=LET(r,MATCH(TRUE,EXACT(A2,AppID),0),IFERROR(INDEX(AddProgramInfo,r),B2))

[#5]

Thanks Patrick! almost there! The formula needs to lookup column A in 'Data' tab against column J in 'Lookup' tab and return column K in 'Lookup' tab. If the return gives an error, then it should return column B in the 'Data' tab. Hope my explanation is ok!

[#6 · Accepted solution]

Yes, it wasn't clear why the original VLOOKUP included column L so I thought that might've been the return you wanted. I've updated my previous post.

[#7]

Thank you so much Patrick! Much appreciated and has saved me so much time : )

[#8]

You're welcome!