Compare cell contents in different columns, then fill neighbouring cells

[BravoBravo]

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!!

[azumi]

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)

[azumi]

Or see the file please...


Thanks

[BravoBravo]

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.

[azumi]

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