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.

Answer:

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:

COUNTIF(range,criteria)
Counts the number of cells within a range that meet the given condition

IF(logical_test;[value_if:true];[value_if_false])
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

INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

Related posts:

1. Lookup between two lists of data to highlight missing data using conditional formatting in excel
2. Looking up data in a cross reference table in excel
3. Create and sort distinct list by adjacent cell value size
4. Combine data from multiple sheets in excel
5. Create a list of distinct values from a list where an adjacent cell value meets a criteria in excel
6. How to create a random list of unique numbers in excel
7. Filter a column and create a new unique list sorted from A to Z using array formula in excel
8. Create a list of all numbers or text in a column in excel
9. Create unique distinct list sorted based on text length using array formula in excel
10. Create a unique distinct alphabetically sorted list, extracted from a column in excel