how to convert vlooup to match?

[ZionX]

Good days, recently i wrote a loop function for vlookup, and is appear to work correctly, but it consume a lot of time, especially when processing huge amount of data, here is the code:

For cnt02 = r To IC02 + 1
    Range(Cells(p, 1), Cells(IR04, 1)) = Cells(p - 1, cnt02)
    For cnt03 = p To IR04
        Cells(cnt03, 1).Value = Cells(cnt03, 1) & Cells(cnt03, 2)
    Next cnt03
    Set rng01 = Range(Cells(p, cnt02), Cells(IR05 + p, cnt02))
    rng01 = Application.VLookup(Range(Cells(p, 1), Cells(IR05 + p, 1)), Worksheets("CBOM").Range("A:D"), 4, 0)
Next cnt02

and i know match function can perform same task as vloookup with save a lot of time, but after half of days keep on googling, i still din get hows the match work, can any one please help me up??? thank you so much.

 

[Rijnsent]

Hi there,
just a match probably won't replace the Vlookup entirely... You'd want a combination of MATCH and OFFSET for that. First the match to find the right row and then the offset to get the right information.
What could also speed up your macro: make the range in your formula ("A:D") dynamically smaller (searching in e.g. A10:D50 is much faster).
Cheers,
Koen