Search code examples
excelsearchif-statementexcel-formulaworksheet-function

Locating a string and copy adjoining cell to new column

I want to look for a cell that contains a string and if it does then take a number in an adjoining cell on the same row and copy this to a new column. I know very little about Excel but tried: 

=IF(FIND("sja",D14),M14=E14,M14=0)

and it didn't work. This is just for one row, but I hope to apply this to the entire column.

Basically I have a list of purchases and want to extract the amount from certain suppliers to a new column.

How might I achieve this?

Solution

  • The following, or similar, may suit:

    SO16689364 example

    The search term is in Row1 (M and N) to allow the option of searching for different terms without overwriting or hard coding. The asterisks are to search for the term regardless of context. SEARCH also produces numeric output but combined with >0 should be TRUE or FALSE (if not an error). If there is an error the default is blank (""). In case D may contain multiple instances summation is with a separate formula (here in Row18).