In this example we are going to use two lists with identical columns, shown in the image above. It is easy to modify the COUNTIFS function if your columns are not arranged in the same order, see the COUNTIFS explanation below.
Create named ranges
There is really no need for named ranges in this example, although they shorten array formulas considerably. You can skip this step if you want.
Select A2:A13 on sheet "List 2"
Type Year in Name Box
Repeat with remaining ranges:
Sheet: List 2 , Range:B2:B13, Name: Asset
Sheet: List 2 , Range:C2:C13, Name: Cost
Sheet: List 1 , Range:A2:A11, Name: Year1
Sheet: List 1 , Range:B2:B11, Name: Asset1
Sheet: List 1 , Range:C2:C11, Name: Cost1
The IF function has three arguments, the first one must be a logical expression. If the expression evaluates to TRUE then one thing happens (argument 2) and if FALSE another thing happens (argument 3).
Array formula in B15: =INDEX($B$3:$B$12, MATCH(0, COUNTIF($B$14:B14, $B$3:$B$12)+IF(((COUNTIF($D$3:$D$11, $B$3:$B$12)>0)+(COUNTIF($F$3:$F$12, $B$3:$B$12)>0))=2, 0, 1), 0)) Copy cell B15 and paste it to […]