-2

I'd like to be able to search a column (say A) cell-by-cell and, if the cell contains certain strings (say "apple" or "pear"), print a certain value (say "A" or "P") in the corresponding cell of another column (say B).

So, if A1 has "pear", then B1 will say "P". If A1 has "apple", B1 will say "A".

What formula can I use to accomplish this?

j400nb
  • 17
  • 5

1 Answers1

1

You need a lookup table in order to do the transformation. Put in cell E2 the following formula:

=XLOOKUP(A2:A5,C2:C3,D2:D3,"")

and here is the output:

sample excel file

In case the fruit was not mapped, then it returns an empty string.

If you don't have in your excel version XLOOKUP, then you can use instead:

=IFERROR(VLOOKUP(A2:A5,C2:D3,2,0),"")
David Leal
  • 6,373
  • 4
  • 29
  • 56
  • Great, thank you! Is there a way to have it do the same thing if the text isn't exactly the same as the search term but has that term embedded in a longer string? So, say the string could be "Apple", "xxxApple", "xAppleX", etc. – j400nb Oct 28 '22 at 18:46