I have 2 columns of data - one has either the word Waived or MVP and the other has either the word Waived or MEC

What I need is for it to look at both columns and return either Waived if "Waived" is in both columns, MVP if "MVP" is in the first column, or MEC if "MEC" is in the second column

I have tried many different variations of an IF statement after researching my issue on the forums and trying some of the suggestions for other people's issues, but it always gets through the first 2 logic tests and since the first column does have waived it only returns waived instead of MEC.

The current iteration that I have is

=IF(OR((Z2="Waived", "Waived")* (Z2="MVP", "MVP")* (AA2="Waived", "Waived")* (AA2="MEC", "MEC"))

but this is erroring out, so I am at a loss what I should do

I have 2 columns of data - one has either the word Waived or MVP and the other has either the word Waived or MEC

What I need is for it to look at both columns and return either Waived if "Waived" is in both columns, MVP if "MVP" is in the first column, or MEC if "MEC" is in the second column

I have tried many different variations of an IF statement after researching my issue on the forums and trying some of the suggestions for other people's issues, but it always gets through the first 2 logic tests and since the first column does have waived it only returns waived instead of MEC.

The current iteration that I have is

=IF(OR((Z2="Waived", "Waived")* (Z2="MVP", "MVP")* (AA2="Waived", "Waived")* (AA2="MEC", "MEC"))

but this is erroring out, so I am at a loss what I should do

• if-statement
• excel-formula

• Try =IF(Z2="MVP","MVP",IF(AA2="MEC","MEC","Waived")) assuming there are only 3 possibilities: MVP-Waived, Waived-MEC, and Waived-Waived. – VBasic2008 Commented Feb 8 at 1:29

2 Answers 2

What I need is for it to look at both columns and return either Waived if "Waived" is in both columns, MVP if "MVP" is in the first column, or MEC if "MEC" is in the second column

You can use:

=IFS(AND(Z2="Waived",AA2="Waived"),"Waived",Z2="MVP","MVP",AA2="MEC","MEC")

Or

=IFS(Z2&AA2="WaivedWaived","Waived",Z2="MVP","MVP",AA2="MEC","MEC")

Another short way could be: =@SORT(Z2:AA2,,,1)

Or spilling: =BYROW(Z2:AA4,LAMBDA(b,@SORT(b,,,1)))

Or =SUBSTITUTE(BYROW(Z2:AA4,CONCAT),"Waived",,1)