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

# Lookup Formula Syntax for Name Replacement

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 A COL B Tracy Jones Cullen Huffman Lady Luck Hayley Shaffer Betty Boop Dexter Thomas Mister Magoo Elizabeth Jacobson Bart Simpson Legacy Wilkins Jennifer Jones Amalia Watson Daphne Kitty Greyson Gomez Peter Piper Clementine Bridges Ronald McDonald Kenneth Morales

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

# Re: Lookup Formula Syntax for Name Replacement

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!

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

This will help all forum participants.

# Re: Lookup Formula Syntax for Name Replacement

``=XLOOKUP( name, colA, colB, name )``

# Re: Lookup Formula Syntax for Name Replacement

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

# Re: Lookup Formula Syntax for Name Replacement

yw
1 best response

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

# Re: Lookup Formula Syntax for Name Replacement

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!

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

This will help all forum participants.