Vlookup using short and full name with case insensitive

Hello unique369​ ,

Happy Holidays to you.

I don't fully understand what you are trying to accomplish but I think if you would answer some of these questions maybe the images becomes clearer and we can all chime in with ideas:

A) What is this PRODUCTION NAME LIST in cell A2 in the first image?

b) Why do you have a #NAME? error in cell B2 in the second image?

What you can do is:

A) Compare two string of the same case (either UPPERCASE or LOWERCASE) and see if they match. You can use the UPPER() or LOWER() functions and and you would say =UPPER(Source!A3) = UPPER(Sheet2!A3) If what is in these two cells are equal then you can use the PROPER() function to make it Proper case (The 1st letter of every word is UPPERCASE)

You can also check the EXACT() function, it maybe helpful also.

By Default XLOOKUP (and you should DEFINITLY use XLOOKUP vs VLOOKUP) is NOT case sensitive, but you can make it be case sensitive by wrapping the lookup value in an EXACT() function like this:

=XLOOKUP(TRUE, EXACT(A3:A13, C3), A3:A13, "Not Found") You are looking for the ID to match on.

So, try these combination of functions and see how far you get with them.

We are always here to guide you and leaned a hand.

GiGi.