0

I’ve got two columns “O” and “P” in a google spreadsheet.

I’m trying to fill column “O” with text based on numerical input in column “P” e.g. if “3” is typed into cell P3, fill cell“O3” with text “Laser, punch, debur in progress”, here’s what I tried:

=ARRAYFORMULA(IF(P2:P<>"",IF(P2:P = "1", "CAD Programming in progress",if(P2:P = "2", "CAD Programming finished", "in progress")),""))

Works but I get to use only two arguments.

=ARRAYFORMULA(IF(P2:P<>"",IF(P2:P="1","CAD Programming in progress"), if(P2:P="2","CAD Programming finished", if(P2:P="3","Laser, puch, debur in progress", if(P2:P="4","Laser, puch, debur finished", if(P2:P="5","Fold in progress", if(P2:P="6","Fold finished", if(P2:P="7","Bush in progress",))))))))

Works only for the first argument and all others return FALSE.

=ARRAYFORMULA(IF(P2:P<>"",IF(P2:P = "1", "CAD Programming in progress",if(P2:P = "2", "CAD Programming finished",if(P2:P = "3", "Something 3 in progress","in progress")),"")))

Wrong number of arguments to IF. Expected between 2 and 3 arguments, but got 4 arguments.

Here is the link to the spreadsheet:

https://docs.google.com/spreadsheets/d/1gMMH3UUAPmGKIc4V-tGddy5XokROjXvoKyhdCjQv0X4/edit?usp=sharing

And here is a link for what text needs to be filled for every number:

https://docs.google.com/spreadsheets/d/1tbxLuKe6-MdndI25wl7COzBvCuKBdU9Wi6LFdQsl2F0/edit?usp=sharing

0

Combine the two worksheets into one workbook and use INDEX/MATCH:

=INDEX(COLOURS!A:A,MATCH(P2,COLOURS!C:C,0))

This will use the table with the codes and expected outputs to fill in the correct value in Column O.

2
  • Thank you Scott, I tried that but I got an error: "Did not find value '1' in MATCH evaluation." Also I'd need that to be autofilled down the entire "O" column and I'm not sure if your formula would do that, please?
    – Marcin
    Oct 2 '16 at 22:44
  • Hi Scot, thank you! I'd like to PASTE (not drag down) your formula [=INDEX(COLOURS!A:A,MATCH(Q2,COLOURS!C:C,0))] to any cell so that is always uses the code from the parallel cell. e.g. if I pasted that formula to P225 it would take the code from Q225, how to achieve that please?
    – Marcin
    Oct 5 '16 at 23:58
0

I don't have enough of a reputation yet to comment, so building on Scott's response you had the formula in the wrong column, and you didn't have your column P formatted as a number. I made the edit in your sheet.

enter image description here

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.