change cell value with SUBSTITUTE formula

Carica · 01/28/25

Hello!

Trying to change the column (submitted by) values from user name to the actual persons name. I have:

IF([submitted by]@row="{users log in}", SUBSTITUTE ({first and last name}))

I'm getting no return value.

thank you

Paul Newcome · 01/29/25

In that case, you would use an INDEX/MATCH.

=INDEX({Reference Sheet Name Column}, MATCH([Column2]@row, {Reference Sheet Match Column}, 0))

Paul Newcome · 01/28/25

There is no way to access "current user" via formula.

Carica · 01/28/25

thanks. I have a sheet that matches user name to actual name that i want to cross-reference, or link in the sheet where the name change or substitution is need.

Carica · 01/28/25

i hope that my last replies clarifies.
im in the sheet, and i want to change the user value to the user full first,last name based on the existing sheet that has these values matched. My formula is above, and its not working. Please advise. Thanks!

Paul Newcome · 01/28/25

Are you able to provide some screenshots for context? It sounds like you may need an INDEX/MATCH instead.

Carica · 01/28/25

here: in column 3 i would like to have the first, last name. Column 4 and column 5 are a separate sheet, listing user names and their matching full name.

IMG_0830.png

Uploaded Jan 28, 2025266.33 KB

Paul Newcome · 01/29/25

In that case, you would use an INDEX/MATCH.

=INDEX({Reference Sheet Name Column}, MATCH([Column2]@row, {Reference Sheet Match Column}, 0))

Carica · 01/29/25

thats the correct formula. Thank you!

Paul Newcome · 01/29/25

Happy to help. 👍️

Carica · 03/04/25

hi @Paul Newcome, all of a sudden this formula is giving #no match return, for all values. Do you known what may be the cause? Thanks

Paul Newcome · 03/04/25

It means that [Column2] is no longer found in the {Reference Sheet Match Column}.

Has anything changed with the source data?

Carica · 03/04/25

ok, thanks! Nothing has changed. I copy pasted the sheet and the initial formula referenced, and it worked.
thank you!

Carica · 03/04/25

@Paul Newcome on another note, what do you think about the drop down menu column on the form with 1000 selections. How to make the field on the form user friendly? Or searchable?

Thanks

Formulas and Functions

Formulas and Functions

change cell value with SUBSTITUTE formula

Best Answer

Answers

Help Article Resources

Quick Links

Categories

Trending in Formulas and Functions

Finding if a date falls in a range in a referenced sheet

CountIF contains name from a contact list help needed

Formula for averaging days