Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community
SOLVED

Lookup Formula Syntax for Name Replacement

Copper Contributor

Hello,

 

I've got a column of names and a matching column of aliases for those names.  It looks something like that below.  What I want to do is use these two columns as a lookup key for another sheet.  On another sheet, I want to replace every instance of a name in COL A with the one in COL B for that row.   My sheet is far too long for a find/replace and there are 189 names in my list.       Thank you in advance for your help. 

COL ACOL B
Tracy JonesCullen Huffman
Lady LuckHayley Shaffer
Betty BoopDexter Thomas
Mister MagooElizabeth Jacobson
Bart SimpsonLegacy Wilkins
Jennifer JonesAmalia Watson
Daphne KittyGreyson Gomez
Peter PiperClementine Bridges
Ronald McDonaldKenneth Morales

 

 

4 Replies
best response confirmed by GlatzsSa (Copper Contributor)
Solution

@GlatzsSa 

To achieve this, you can use the VLOOKUP function or the INDEX and MATCH combination in Excel. Assuming your data is in Sheet1 and you want to replace names in Sheet2, here's an example using VLOOKUP:

=VLOOKUP(A2, Sheet1!A:B, 2, FALSE)

This formula assumes that the names in Sheet1 are in column A, and the corresponding aliases are in column B. Drag this formula down in the column where you want to replace names, and it will replace each name with the corresponding alias.

If you prefer to use INDEX and MATCH, the formula would look like this:

=INDEX(Sheet1!B:B, MATCH(A2, Sheet1!A:A, 0))

Again, replace the sheet and column references as per your actual data.

Just ensure that the reference ranges and sheets match your actual data structure.

AI was partially deployed to support the text.

 

My answers are voluntary and without guarantee!

 

Hope this will help you.

Was the answer useful? Mark as best response and Like it!

This will help all forum participants.

@GlatzsSa 

=XLOOKUP( name, colA, colB, name )

@NikolinoDE THis worked: =VLOOKUP(A2, Sheet1!A:B, 2, FALSE)
I love it.  Thank you!

1 best response

Accepted Solutions
best response confirmed by GlatzsSa (Copper Contributor)
Solution

@GlatzsSa 

To achieve this, you can use the VLOOKUP function or the INDEX and MATCH combination in Excel. Assuming your data is in Sheet1 and you want to replace names in Sheet2, here's an example using VLOOKUP:

=VLOOKUP(A2, Sheet1!A:B, 2, FALSE)

This formula assumes that the names in Sheet1 are in column A, and the corresponding aliases are in column B. Drag this formula down in the column where you want to replace names, and it will replace each name with the corresponding alias.

If you prefer to use INDEX and MATCH, the formula would look like this:

=INDEX(Sheet1!B:B, MATCH(A2, Sheet1!A:A, 0))

Again, replace the sheet and column references as per your actual data.

Just ensure that the reference ranges and sheets match your actual data structure.

AI was partially deployed to support the text.

 

My answers are voluntary and without guarantee!

 

Hope this will help you.

Was the answer useful? Mark as best response and Like it!

This will help all forum participants.

View solution in original post