Calculating whether a person has upgraded between lists

Hello,

I am trying to write a formula that isn't a gigantic IF statement to calculate whether a person has upgraded their type of house or not within a time period.

The below are the names of the types of housing. I have two of these lists, the first is from the beginning of the time period and the second is from the end of the time period:

Pucca - Normal
Kuchha - Strong
Pucca - IAY
Kuchha - Medium
Kuchha - Weak

The best option on this list is Pucca - Normal and the worst is Kuchha - Weak.

A person can start at any option on this list and end on any option. I only want to measure whether their type of house has been upgraded (i.e. from Kuchha - Weak to Kuchha - Medium). I do not need to calculate whether they have stayed the same or been downgraded.

If the person has upgraded I want to display the value of 1. If they have stayed the same or downgraded I do not want to display anything.

An added complication is that if they have upgraded to Pucca - IAY I want to also do not want to show anything.

I know this might sound a little weird but can anyone help?

Thanks

Steve

Thanks very much Olly. That has worked except for one thing.

I need to be able to show a value of 0 if they have upgraded to Pucca - IAY.

I have tried to do this by creating two lists 1. without Pucca - IAY and 2. with Pucca - IAY at the end. I have given these different names so that the user selects from the list including Pucca - IAY but the calculation uses the list that excluded Pucca - IAY. The issue is that when someone does upgrade to Pucca - IAY I get an #N/A error.

This is the formula so far, it is basically a carbon copy of yours =IF(MATCH('Base line survey - completion'!C21,House,0)<MATCH('Base line survey - initial'!C21,House,0),1,0)

Do you know how I can get rid of this #N/A error?

Thanks

Steve