Vlookup using short and full name with case insensitive

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