If Cell Includes Text, Display as Other Text

[Lance Devore]

Hello.

If a cell includes the text shown in A1, I need it to display the text in B1 in that cell instead.

In other words, if D1 contains A1, then replace D1 with the text in B1. If D1 contains A2, replace D1 with the text in B2. Etc.

So for the example in my attached spreadsheet.
D1 should become "birds"
D2 should become "fish"
D3 should become "dogs"
D4 should become "cats"

(this is just an example. In my real spreadsheet, the word in B will not always be in A. So I do need the function to search D for the text in A, and then display B)

What's my best method for achieving that?

Thanks.

[Pepe Le Mokko]

Administrative note

Welcome to the forum

in your haste to solve your problem, you probably missed the yellow banner advising how to get answers faster by posting a sheet ?

Please take a moment to read it and attach a sheet accordingly.

Thanks you for helping us help you

[Lance Devore]

Sorry about that. I've updated the post with an attached sheet. Thanks.

[JBeaucaire]

Try this function in E1, then copied down:

=LOOKUP(2, 1/(ISNUMBER(SEARCH($A$1:$A$4,D1))), $B$1:$B$4)

In the case of multiple matches, it will return the LAST match from the list in column A.

[Lance Devore]

That works! Thanks so much. Now one problem. In my sheet there may be a case where E7, for example, already has "Goats" pre-filled in and there's no statement in D7. This function is is overwriting "Goats" to say "N/A" because there was no statement in D7. Is there a way for the function to do nothing if it doesn't find a statement in D7?

[Limor_OP]

Hi,
you can add iferror into the existing formula:

=IFERROR(LOOKUP(2, 1/(ISNUMBER(SEARCH($A$1:$A$4,D1))), $B$1:$B$4),"")

[Lance Devore]

Perfect, thank you! That's exactly what I needed.

[Limor_OP]

Great! glad this helps, now I need to understand JBeaucaire 's formula

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

[JBeaucaire]

I'ts a trick Excel formula. You can see it in action by selecting the cell, then going to FORMULAS > EVALUATE FORMULA, then step through it one calculation at a time and watch it unfold from the inside out.

A normal Lookup finds a value in one range, then returns a value from an adjacent range.

In this trick version, the ISNUMBER(SEARCH) part is testing every search value in column A against the searchable string. The result is an array if TRUE/FALSE answers.

When you divide the number 1 by the TRUE/FALSE answer array, you get a new array of decimal values and errors.

Since the LOOKUP value is 2, that value never occurs in a series of decimal values, so LOOKUP defaults to the last closest value and uses that as the position of resolution. That position is then pulled from the adjacent range.

In short, this trick always finds the "last useable match" in an array of possible matches.

[Limor_OP]

Very clever, and thank you for the extensive explanation.