One way that might give you some ideas to customize

I did a hidden sheet with two col's called Translate, and put a Change event on the sheet that I wanted to use.

If you enter a 'known' value, this will put a 'looked up' value immediatlt to it's right


If you enter a 'unknown' value in a cell, then if you immediately enter a value to it's immediate right, both are added to the translate table


[vba]
Option Explicit
Dim rPrevious As Range
Private Sub Worksheet_Change(ByVal Target As Range)
Dim v As Variant


'is this one immediatedly after a 'not found' and in the cell to the right
If Not rPrevious Is Nothing Then
If Target.Cells(1, 1).Address(1, 1, 1, 1) = rPrevious.Offset(0, 1).Address(1, 1, 1, 1) Then
Worksheets("Translate").Cells(1, 1).End(xlDown).Offset(1, 0).Value = rPrevious.Value
Worksheets("Translate").Cells(1, 2).End(xlDown).Offset(1, 0).Value = Target.Cells(1, 1).Value
Set rPrevious = Nothing
End If
End If


'see if we're lucky and this value is in the table
On Error Resume Next
v = Application.VLookup(Target.Cells(1, 1), Worksheets("Translate").Range("A:B"), 2, False)
On Error GoTo 0

'if it's in the table, put it in the cell to the right
If Not VarType(v) = vbError Then
Target.Cells(1, 1).Offset(0, 1).Value = v
Set rPrevious = Nothing
Exit Sub
End If
'save this cell
Set rPrevious = Target.Cells(1, 1)
End Sub
[/vba]


Paul