*Articles/Excel101*

Excel training

Written: Mar-2024

# IF and CONCAT

On this step we want to use the two Excel functions `IF(a,b,c)`

and `CONCAT(a,b,c)`

function. The arguments for these are as follows:

`=IF(a,b,c)` | Test a and if it's true then return b, otherwise return c, e.g. `=IF("a"="b", 1,0)` will check if "a" = "b" and as they are different then it will return "0" |

`=CONCAT(a,b)` | Concatenate the text strings a and b, and any number of other strings if you include them, e.g. `=CONCAT("a","b","c","d")` will return "abcd" |

#### Steps

- add another new worksheet (Sheet3)
- on cell
`A1`

we are going to build up a multi-point formula starting with the text`=IF(`

. Then while still focused on the Formula Bar input field, click on the Sheet1 sheet from the worksheets tabs at the bottom of the workbook, then select cell`E2`

. Then continue typing in the Formula Bar to add ...`=1,CONCAT(`

then click on cell`B2`

(resulting in`=IF(Sheet1!E2=1,CONCAT(Sheet1!B2`

). Continue this with`," ",`

to concatenate a space after the`B2`

name, and then click on cell`D2`

to make`=IF(Sheet1!E2=1,CONCAT(Sheet1!B2," ",Sheet1!D2`

. Finish this concatenation function with a`)`

. This completes the CONCAT function which was the positive action of this IF function, but we also need to add in the negative action, so finish off the formula with`,"na")`

to make the final formula :`=IF(Sheet1!E2=1,CONCAT(Sheet1!B2," ",Sheet1!D2),"na")`

To review this IF we should understand the following parts of the formula:`=IF(Sheet1!E2=1,CONCAT(Sheet1!B2," ",Sheet1!D2),"na")`

...if`=IF(Sheet1!E2=1,CONCAT(Sheet1!B2," ",Sheet1!D2),"na")`

...test`=IF(Sheet1!E2=1,CONCAT(Sheet1!B2," ",Sheet1!D2),"na")`

...positive result`=IF(Sheet1!E2=1,CONCAT(Sheet1!B2," ",Sheet1!D2),"na")`

...negative result

Press return on the formula bar to commit this - Drag this new cell value down from
`A1`

to`A20`

- To improve this further - we can add in a format instruction onto the salary part of the CONCAT - using the format
`£#,###0`

as the second argument to the function "TEXT(a,b)", e.g.`=IF(Sheet1!E2=1,CONCAT(Sheet1!B2," ",TEXT(Sheet1!D2,"£#,###0")),"-")`

*square*

*chevron_left*Step-17-add-a-Pie-Chart-graphic

ADDITIONAL-Work-with-times

*chevron_right*
## Comments

## New Comment