A formula that combines the following functions: VLOOKUP, IF, AND

Hello there, thanks for reading.

I've come across a problem that I get the feeling should be simple but just can't get my head around. All the formulas I have tried so far have brought up the #value error if anything at all.

Basically I want a formula that automatically inputs a code based on 'marital status'. This would be easily done using a vlookup except for the fact that one of these marital statuses has multiple options. Example: if the marital status reads "Married" the code can very depending on whether it is different gender/same gender. I have gender codes in a separate column.

For example:

Status--->Code
Single---------------->Sin
Civil P'ship------------>Civ
Married + MM--------->Mar 1
Married + FF---------->Mar 1
Married + MF--------->Mar 2

Married is, of course, in a separate cell from the gender codes, which is why I've attempted to bring the AND function into the IF statement. However, I’m having no luck.

The spreadsheet I am designing is already getting pretty big so I wanted to avoid having more columns than necessary. Is it even possible to group this together in one formula?

This is an example of a formula I have tried but returns the #value error.

=VLOOKUP(I31,Mapping!$A$1:$C$12,3,FALSE)+IF(AND(I31="Married",AH31="MM"),"MAR 12","")

Any help with this would be greatly appreciated.

Thank you for your time

Marital status in A, Genders in B you could use the formula (untested)

Formula:

Please Login or Register  to view this content.

Refer attached workbook. Col F contains the required formula. Is this something that you can work with?