Multiple IF statements and LEFT function in one cell

[roadwreck]

Hello

In column C i have ID numbers. The first digit refers to gender and in column D needs values based on that. If it is male than "M" and if female than "F".

3 and 5 - male
4 and 6 - female

I have tried using different IF nesting functions but haven't found one that works. For example =IF(LEFT(C3;1)=3;5;"M";IF(LEFT(C3;1)=4;6;"N")), but this says that too many arguments for this function.

Hopefully someone can lead me to the right path with this

[AliGW]

Administrative Note:

Members will tailor the solutions they offer to the version of Office (Excel) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this. Thanks.

Although we value your privacy as much you do, it could be important that members have a rough idea of your location as the solutions they offer may be affected by your locale. For instance, you might in the future post questions which are related to your regional settings.

With this in mind, please update your profile to something more precise (something such as UK, Europe, USA, UAE, etc. will suffice).

Thank you for helping us to help you.

[AliGW]

A bit less clumsy perhaps:

=LOOKUP(--LEFT(C3,1),{3,4,5,6},{"M","F","M","F"})

Otherwise you will need two OR statements.

You may need to replace commas with semi-colons for your locale (which is why your location is important to us).

[JohnTopley]

Try

=IF(OR(LEFT(C3,1)="3",LEFT(C3,1)="5"),"M",IF(OR(LEFT(C3,1)="4",LEFT(C3,1)="6"),"F","N"))

change "," to ";"

Please update LOCATION in your profile

[roadwreck]

Thank you AliGW and JohnTopley!

Both solutions work as needed.

Many thanks again

[queuesef]

copy to cell F3, then copy down.

=IF(OR(VALUE(LEFT(C3,1))=3,VALUE(LEFT(C3,1))=5),"M",IF(OR(VALUE(LEFT(C3,1))=4,VALUE(LEFT(C3,1))=6),"F","neither"))

I like Ali's (Does it (ali's) work if it is none of the numbers? )

[AliGW]

I like Ali's (Does it (ali's) work if it is none of the numbers? )

Perhaps:

=IFERROR(LOOKUP(--LEFT(C3,1),{3,4,5,6},{"M","F","M","F"}),"Non-Gender")

Thank you AliGW and JohnTopley!

Thanks for the rep.