Replacing references in two columns

I have 2 colums like this. And I want whenever there is a reference like this βλ. "some word" in column 2, to have the macro look up some word entry in column 1, find the text in the same row in column 2 and replace the βλ. "some word" reference with the text found in column 2. (See attached, sheet 1 as it is, sheet 2 as I want it to be).

Sheet 1

another word βλ. "some word"
aword μετάφραση
bword βλ. "aword"
some word κάποια λέξη

Sheet 2

another word κάποια λέξη
aword μετάφραση
bword μετάφραση
some word κάποια λέξη

Hi,

use this formula in C1 and copy down

=IF(LEFT(B1,3)="βλ.",VLOOKUP(MID(B1,6,LEN(B1)-6),$A$1:$B$100,2,FALSE),B1)

Column C now has the list you want. You can then copy column C and use Paste Special - Values to paste the list into colum B. Finally delete Column C, as it is no longer needed.

does that help?

Thanks, tried it but gives me an error:

http://lh6.ggpht.com/_Z6S8BcbaPTU/Su...-30_000001.png

Replaced all the commas with ; and now at the cells with the reference it gives me:

#N/A

and the other cells simply copying what is in column 2.

Check the formula again. compare with the attached. Although this is Greek to me, the formula seems to work ...

For some strange reason no, even when I add the list in your sheet and drag it down.
Although with the 4 rows in your sheet it does work.

I am guessing here, is it because at least in one of the cases, there is no exact same reference value, and as a result, all other instances fail?

http://lh6.ggpht.com/_Z6S8BcbaPTU/Su...-30_024950.png

See attachment.

there is no exact same reference value, and as a result, all other instances fail?

not really. If the formula bombs in one cell, the others should be unaffected. Can you post a sample of your real data with the error appearing?

I have done so; in the post above.

Ahhh . Found it

=IF(LEFT(B1,3)="βλ.",VLOOKUP(MID(B1,6,LEN(B1)-6),$A$1:$B$4,2,FALSE),B1)

This is the formula in C2. You need to change the red bit to be the last row of your list. If your list is 1000 words, it'll be $B$1001. Currently the formula works only for the first 4 rows

hth

Yep, it works, many thanks!