Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

if and

ResolvedVersion 2016

Maisie has attended:
Excel Intermediate course
Excel Advanced course

IF and &

I am getting an errors NAME?

Cell H9 has HKD
Cell I9 has 788
Formula in K9 =IF(H9=HKD,I9&" HK equity",F)

I'm trying to get the results 788 HK equity

Also having trouble with a nested IF formula
Cell H10 has USD
Cell I10 has BILI
Cell H11 has CNY
Cell I11 has 000001
Formula in K10 =IF(H10=HKD,I9&" HK equity",IF(H10=USD,I10&" US equity",IF(H10=CNY,I11&" CH equity")))

I'm trying to get results in cell K10 of BILI US equity
I'm trying to get results in cell K11 of 000001 CH equity

The cell formats are text.



RE: IF and &

Hi Maisie,

Thank you for the forum question.

Change this:
Formula in K9 =IF(H9=HKD,I9&" HK equity",F)

To:
Formula in K9 =IF(H9="HKD",I9&" HK equity","F")

All text must be inside ""

And change:

Formula in K10 =IF(H10=HKD,I9&" HK equity",IF(H10=USD,I10&" US equity",IF(H10=CNY,I11&" CH equity")))

To:

Formula in K10 =IF(H10="HKD",I9&" HK equity",IF(H10="USD",I10&" US equity",IF(H10="CNY",I11&" CH equity")))



Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Excel tip:

Quickly copy a formula across sheets

Suppose you have a formula in cell Sheet1!B2, say =A1*5%, that you wish to copy to cell B2 on Sheet2, Sheet3 and Sheet4. Instead of using copy and paste, try this: (1) Select Sheet1!B2. (2) Group Sheet1 with the worksheets Sheet2, Sheet3 and Sheet4 by holding down Ctrl and clicking on the tabs of the sheets to group them. (3) Press the F2 key, then immediately press Enter to copy the formula in Sheet1!B2 across the grouped sheets.

Remember to ungroup the sheets afterwards! Right-click on any tab and choose Ungroup Sheets to do that.

View all Excel hints and tips

Connect with us:

0207 987 3777

Call for assistance

Request Callback

We will call you back

Server loaded in 0.1 secs.