Vlookup
Posted by HR Eddens on December 27, 2001 9:50 AM
How can I force vlookup to move past the first match? I have a list that has repeated values that I want to find matches for. I have tried vlookup and Index & Match but both techniques require a unique key field for searching.
Posted by Jacob on December 27, 2001 11:17 AM
Hi
VB can do this easy with .findnext, but with functions lets see??
lets say your array is from A1:C100 in column D1:D100 put A2:A100, so in D1 you have A2 and so on. Dont put =A2, just A2. it needs to be the next row so D50 would have A51 in it.
now do a vlookup in lets say E1 and return the value in the fourth column. so if it finds the value in A30 it would return what is in D31.
In F1 put = D1&":"C100" This should give the range from the row below the first vlookup find and C100;
now do another vlookup but for the range type indirect(F1) this should start the second vlookup after where the first value was found.
Hope this gets you started.
Jacob
Posted by Jacob on December 27, 2001 11:19 AM
Typo: This one is right
Hi
VB can do this easy with .findnext, but with functions lets see??
lets say your array is from A1:C100 in column D1:D100 put A2:A100, so in D1 you have A2 and so on. Dont put =A2, just A2. it needs to be the next row so D50 would have A51 in it.
now do a vlookup in lets say E1 and return the value in the fourth column. so if it finds the value in A30 it would return what is in D31.
In F1 put = E1&":"C100" This should give the range from the row below the first vlookup find and C100;
now do another vlookup but for the range type indirect(F1) this should start the second vlookup after where the first value was found.
Hope this gets you started.
Jacob
