Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup multiple changes
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup multiple changes
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). -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Kim" wrote: 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup multiple changes
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 '==================== -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Luke M" wrote: 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). -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Kim" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup multiple criteria multiple occurrences sum values | Excel Worksheet Functions | |||
Vlookup with Multiple criteria and multiple sheets | Excel Worksheet Functions | |||
Vlookup for multiple criteria, multiple worksheets | Excel Worksheet Functions | |||
How do I use VLOOKUP to ref multiple workbooks with multiple tabs? | Excel Discussion (Misc queries) | |||
Multiple vlookup | Excel Discussion (Misc queries) |