Compare cell contents in different columns, then fill neighbouring cells

Hello,

I'm sure this is an easy problem to solve - I just can't work out ANY (let alone a simple) solution.

I have two sheets:
- sheet 1 ('current')
- sheet 2 ('31May')

The data in column C is the same, although sorted differently and can't be re-sorted to be the same. Where the data in column C is the same in both sheets, I want to fill columns A and B into sheet 1. Please see the example attached (which is probably more clear than that description ).

Any help would be greatly appreciated!!

Put in A2 copied down and copied cross to column B:

=LOOKUP(2^15,SEARCH('31May'!$C$2:$C$26,$C2),'31May'!A$2:A$26)

Or see the file please...


Thanks

Thanks Azumi - this solution appears to work only intermittently in the 'live' data. In a majority of cells, I am getting an error 'value not available'.

Perhaps I should've added there is over 250 rows of data in the full workbooks? Does this have some impact?

I don't understand the '2^15' at the start of the formula - can you tell me what this is for?

I'm using Excel 2010.

Yes you can add rows as you like, thats non array formula, so hope it light and fast, the explnation maybe like this:

SEARCH function searches values for C2 in ranges '31May'!$C$2:$C$26, and it will return number when found it.

Sorry with my bad english lol

2^15 meaning that the largest number of character that can fit in a cell is or with number is 32767, so have LOOKUP(2^15 that will find largest number than SEARCH can find it, other words you'll find the last matches that contain C2