Write text in adjacent cells if one cell value is ___?

teaguru - Jun 13, 2016 at 11:40 AM
 teaguru - Jun 14, 2016 at 12:38 PM

My knowledge of excel is very basic, but I am interested to know if I can find an easier way to enter information.

I'm a tea manufacturer and I need to submit a list of recipes with supplier names and organic registration information.

Second sheet is called SUPPLIERS LIST and lists the suppliers with the ingredients we purchase from them as well as the certification body.

Third sheet is called RECIPE SHEET and lists all of our recipes.

I've already put in the recipes but next to each ingredient I need to list who the supplier is, if they are organic and who the certification body is.

Looks like this:

A26: Ingredient B26: % of this ingredient in recipe C26: Supplier D26: Certified Organic? E26: Certification Body

A27: Black tea B27: 80% C27: Supplier A D27: Yes E27: Control Union
A28: Apple Pieces B28: 20% C28: Supplier B D28: Yes E28: Ceres

I have 198 recipes and most ingredients appear in multiple recipes. I already have columns A and B filled in. Is there some kind of formula I can write so if Column A is Black tea then columns C, D and E can say Supplier A, Yes and Control Union respectively? And does this become really complicated if I have 100+ ingredients and 4574 rows? Right now I'm doing, Command+F, searching Black Tea and pasting cells C, D and E. It's very time consuming that way!

Any information or tips would be most helpful. It's too bad I can't do something like Find & Replace but for adjacent cells instead of the same cell.

Note: I'm really inexperienced with Excel so please dumb it down for me ;)

1 response

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen January 16, 2023 549
Jun 13, 2016 at 11:53 AM
Hi Teaguru,

Try using the VLOOKUP formula. It will let you search for a value (i.e. Black Tea) in a table of information and return a value of a different column.

Write the formula's for 1 row and then drag those down.

Hopefully this makes sense to you. If not, feel free to ask what you need further assistance with.

Best regards,

That worked, thanks!! :D