0

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?

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

  • 1
    Great, thank you! I just sorted it out using IFS. – Karin Nov 21 '18 at 0:33
1

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."

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.