How do I replace a value that appears in multiple rows if one of the rows contains a certain value in a different column?

[lgreene]

Hi all, I’ve looked everywhere I know to and can’t figure out how to do this. I’m also not entirely sure how to ask.

I am trying to search for a value in one cell and replace another cell based on that value. For example, I want to search in B2 and replace A2 if I find a certain value in B2. But I also want it to replace anything in column A that matches A2.

Below is an example of original data (on the left) and what I want it to end up as (on the right). In this example, it is searching column B for the characters “XYZ.” It is then replacing the value in A2 with “NUM,” since it found “XYZ” in B2, and replacing A1 with “NUM” as well since A1 matches A2. It’s doing this for the others with “XYZ” as well. I did this manually for the example, but I want to do it with a formula, since I’m working in a sheet with nearly 100000 rows.

I know how to search for B2 and replace A2 based on that, but not how to have it replace both A2 and any matches to A2.

Is there a way to do this?

[SM_Riga]

Hello @lgreene

I hope I understood you correctly.

Please find an example attached.

Firstly, you need to filter all values in column A which match desired value in column B. You can use array formula as I did in my example, but this formula is resource consuming, which is definitely bad in case of 100k rows, so if you do not need fully automatic solution, better use Autofilter, select only XYZ value and copy column A result instead of Matched values from A values. The Result formula then just searches each cell value from column A in Matched values from A column. Is match is found, it returns NUM, if not found - cell value from column A.

[lgreene]

This is precisely what I needed. Even though it is too resource-intensive to do every row at once, I can do a few hundred at a time and it’s still far faster than manually looking through the entire sheet.

Thank you so much for your concise answer.