Author: Oscar Cronquist Article last updated on March 12, 2018

Question: i have two sets of data - one has an identifier column and one result column.

A2 data1 B2 22
A3 data2 B3 55
A4 data3 B4 44
A5  data5 B5 22
A6 data4 B6 33
A7 data9 B7 22
A8 data6 B8 44
A9 data88 B9 55

in the second set

D2 data1 E2 33
D3 data2 E3 55
D4 data3 E4 44
D5 data4 E5 33
D6 data88 E6 12

the new list should change B2 from 22 to 33.
If there is no change it shows the first sets result . if there is a change it reports from the second set.
To complicate it... the first set of data is twice as long as the second one. not all data is in the second set and finally the lists are not sorted and cant be.


Create a column with most recent data in excel

Formula in B13:

=IF(COUNTIF($D$2:$D$6, A13)=0, INDEX($B$2:$B$9, MATCH(A13, $A$2:$A$9, 0)), INDEX($E$2:$E$6, MATCH(A13, $D$2:$D$6, 0))) + ENTER

copied down as far as needed.

Download excel example file.
Create a list with most recent data in excel.xls
(Excel 97-2003 Workbook *.xls)

Functions in this article:

Counts the number of cells within a range that meet the given condition

Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

MATCH(lookup_value;lookup_array; [match_type]
Returns the relative position of an item in an array that matches a specified value

Returns a value or reference of the cell at the intersection of a particular row and column, in a given range