Vlookup multiple changes

[Kim]

I was wondering if there is a formulas where I can do vlookup on multiple
changes.
I have a list of unique number and they can change a few time within a year.

For example I have a list of change on sheet 2 as follow:
Old # New #
1 10
3 5
4 7
10 15
15 20

If there a formula where I do a vlookup for 1, it will return 20 in stead of
10.
The logic the original 1 had change a few time and the final number i should
use is 20. At the moment, I have to use multiple columns to get to the number
20.

I need a formula where it will lookup for a number and the do another
vlookup on the new number and see if there is anychange. And if there is, it
will do vlookup until there is no more changes.

Thanks.

 

[Don Guillett]

If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.

 

[Luke M]

You could do this with a UDF. Right-click on sheet tab, view code, then goto
Insert - Module. Paste this in:

'===============
Function TrackChanges(x, r As Range, c As Integer)

Do
xStore = xValue
On Error GoTo EndFound
xValue = WorksheetFunction.VLookup(x, r, c, False)
x = xValue
Loop

EndFound:
TrackChanges = xStore

End Function
'=====================

Close the VBE. Back in your workbook, your formula becomes:
=TrackChanges(1,A2:B20,2)

First arguement is the value to find, second is the range containing your
table, and third is the column within range to look at (similar to VLOOKUP).

 

[Luke M]

Caution: If your changes produce a circular loop like this:
1 became 10
10 became 5
5 became 1

This will throw the code into an endless loop! You can halt the code by
Ctrl+Alt+Pause, and then hitting end. A better UDF might be this:

'=============
Function TrackChanges(x, r As Range, c As Integer)
xCount = 0
Do

xCount = xCount + 1
'Checks for excessive looping
If xCount > 65536 Then
TrackChanges = "#NUM!"
Exit Function
End If

xStore = xValue
On Error GoTo EndFound
xValue = WorksheetFunction.VLookup(x, r, c, False)
x = xValue

Loop

EndFound:
TrackChanges = xStore

End Function
'====================