Forum Discussion
Vlookup using short and full name with case insensitive
Hi Experts,
I have a sheet named Source, with these columns:
Column A: ID
Column B: Full Name
Column C: Department
(data starts from Row 3.)
I have a sheet named Sheet2, with these columns:
Column A: dept with short name
Column B: Full Name
Column C: ID
Column D: Department
I would like to populate the data [full name, ID and department] in sheet Source into sheet2 column B, C and D respectively by matching the short name with the full name. Upper/lower case mismatch and name not match exactly, only can match 2 words. What formula should i use to insert into Sheet2, column B so that I have the information to be automatically filled up in column B, C and D ?
2 Replies
Hi GiGi,
Below is my reply to your feedback A) & b).
A) this is my mistake.
b) Why do you have a #NAME? error in cell B2 in the second image?
<unique reply> This is because Chatgpt has provided a formula for me to insert into cell B2 in sheet2 and it stops from guiding as it reached maximum texts and I cannot afford to upgrade its service.
<unique reply> i do not know where to input your formula =UPPER(Source!A3) = UPPER(Sheet2!A3). What i want to get the outcome is the on sheet2, there is B2, C2, D2 data populated from sheet named Source.
----------------------------------------------------------
I had some correction upon your feedback
I have a sheet named Source, with these columns:
Column A: ID
Column B: Full Name
Column C: Department
(data starts from Row 2.)
I have a sheet named Sheet2, with these columns:
Column A: dept with short name
Column B: Full Name
Column C: ID
Column D: Department
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.
