Conditional Formatting IF (cell column B) THEN (cell column C)

Question

I'm trying to format a Google Sheet to speed up some data entry at my job, tracking the cost of a few different articles, which all have a 3 letter code associated with them, so that all I have to do when entering the data is enter the 3 letter code and the associated cost will appear in the adjacent column.

Basically I'd like to set up conditional formatting that works something like this: IF(B4='SPS') THEN(C4='0.08')

There are 11 different articles with a 3 letter code and associated cost attached, so I'm assuming there would need to be 11 IF rules that would need to apply to the same range of cells, for example: IF(B4='SPS') THEN(C4='0.08'), IF(B4='ACD') THEN(C4='0.33'), IF(B4='FRS') THEN(C4='0.62'), etc.

What is the best way to go about this?

Answer 1

On Google Sheets vocabulary, "conditional formatting" means a very different thing, see Use conditional formatting rules in Google Sheets

Basically you are asking how to write formulas on Google Sheets and more specifically the IF syntax.

For IF(B4='SPS') THEN(C4='0.08') add the following formula to C4

=IF(B4='SPS',0.08,)

For the other 3 letter codes, you could nest several IFs use IFS, VLOOKUP among other functions to get the same result. References

• Add formulas & functions
• IF
• IFS
• VLOOKUP

Answer 2

Using VLOOKUP would be the most flexible. But you can accomplish it for a short list (and 11 is relatively short) using the IFS function like this:

=IFS(B4="SPS",0.08,B4="ACD",0.33,B4="FRS",0.62)

You can add as many condition, result chains as you like.

I still recommend learning how to use VLOOKUP. For instance, if you set up another sheet/tab called "Costs" (or whatever you like), and had header "CODE" in A1 and header "COST" in B1, then added all of your codes in column A with their corresponding costs in column B ...

You could now go to your first sheet and use this type of formula:

=VLOOKUP(B4,Costs!A:B,2,FALSE)

In English, this formula says, "Take the value in B4 and try to find it in the list I have set up in Costs!A:B. If you find it, give me the corresponding value in the 2nd column; oh, and my data isn't in any particular order."