I have data for membership of an organisation in separate arrays by year in Sheets 10, 09, and 08. As there are several members with the same surname [col A], I use Postcode/zipcode [col B] to uniquely identify them.

I would like to create a Table 'All' which shows for all the surname/postcodes found in the 3 tables:

- the surname/postcode in cols A & B,
and then for columns C, D, E called '10', '09', and '08' return either YES or NO if the combination is present or absent from the relevant Sheet '10', Sheet '09', or '08',

thereby identifying continuing and lapsed members.

I got part of the way using MATCH and VLOOKUP but I don't seem able to design the nested formula that would deliver the above result in one or more steps.

Can anyone advise me please?

Thanks