Google sheets-copy from list to all cells adjacent to a specific string

Question

In Google sheets, I have an ingredient price list and a lengthy list of recipes. I'd like to copy the prices from the ingredient list to all of the many many recipes. is there any way to do this in mass. Can I take my value for onions and have it copied to the cell after any cell containing "onions"?

Below is a test sheet, but imagine there are 10,000 recipes and I don't want to hand copy the price of onions.

https://docs.google.com/spreadsheets/d/1yKmcu576FMS3Td1kDJGYyhZYld7Xf-SI2jFf-idCa4U/edit?usp=sharing

Would you show us the problem you have in the question? Presently the only illustration of the problem is in an external link, which we can assume will be modified or deleted in the future. When that happens, the question will have to be repaired or deleted by a volunteer. — halfer

JPV JPV · Accepted Answer · 2017-08-10T06:52:05

Remove the contents of H1 and then enter

={"Cost:"; Arrayformula(iferror(if(len(G2:G), vlookup(G2:G, A:B, 2, 0),)))}

This will return the cost. If you want to return the total cost (by multiplying the cost with the quantity) you can try$

={"Cost:"; Arrayformula(iferror(if(len(G2:G), F2:F*vlookup(G2:G, A:B, 2, 0),)))}

See if that works?

Google sheets-copy from list to all cells adjacent to a specific string

1 Answers