Need help returning specific text value based on criteria

Hi there,

I need help returning the desired result in Column G. Not sure if to nest the IF and SUBSTITUTE formulas.

In the attachment I have two identical list (Text 1 and Text 2). If the values in Text 2 is different than Text 1, i would like the values in Text 2 to supersede the values in Text 1.


Thank you for any help.

Uh, am I missing something. You always want Text 2 then. Formula in G2 would be:
=B2
and copied down.

well the problem is that these two columns I'm comparing are in separate tabs or worksheets. So I do need to find a way of comparing both list and returning the value only if it differs.

I tried to simplify the example (see attachment), I realized it was confusing.

So it seems to me you're saying, if Column B is blank, then show Column A, otherwise, show column B

If you put this formula in the 2nd row (any column) and then copied it down, you would get the same results you are showing in Column E (Your desired results)

=IF(B2="",A2,B2)

what if I only had column A and not column B, how would I be able to return the substituted value in column G? I was hoping there was a way to do it without creating a helper column.

You could create a table of your substitute words, then use this:

=XLOOKUP(A2,Table1[Original],Table1[Substitute],A2,0)
(I put this formula in D2 and copied down)

(the substitute table starts in H5)

See attached.

i love you thank you very much