Find vs Match

[kathyb0527]

I need to find if the value for each cell in a static range in one workbook, is in a dynamic range in another. If it is, copy and paste from workbook one into offset columns in the second workbook. Sometimes there will be a match and sometimes not. What is the best approach, using find, using match or is there another function? What is the difference between these functions?

Thank you for your help?

[westconn1]

there another function?

i believe sumproduct would also do, but i am no expert on that, read the info in the sumproduct subforum

What is the best approach, using find, using match

perhaps you should try both, see which is better /faster

depends if you want to loop using vba or put formula in cells

[Bob Phillips]

Find looks for a particular value within the contents of a single cell, Match looks for a value within a range. Very different beasts.

Your problem is not clear to me, perhaps an example, or some workbooks would help.

[westconn1]

Find looks for a particular value within the contents of a single cell

i assumed he was referring to excels find method, rather than formula

[GTO]

i assumed he was referring to excels find method, rather than formula

Kathy...

Most likely she

[Bob Phillips]

i assumed he was referring to excels find method, rather than formula

I don't know why you would assume that when ...

What is the best approach, using find, using match or is there another function? What is the difference between these functions?

[kathyb0527]

sorry I wasn't very clear. Let's start from the beginning. As part of a larger vba project, I need to look at each cell in Column A (a dynamic range) in worksheet "target" and see if it appears in range B7:B22 in worksheet "Source" if it does, then copy the corresponding value from column E from "source" and paste in corresponding cell in column B in "target". I am unsure of whether to use find or match and if one is more appropriate/easier/faster than the other or if another method (such as westconn1 suggested) would be better.

Thank you for the advice!

[SamT]

VLookUp function

[mancubus]

Hi.

For 1500 lookup values...
Code execution times in seconds

Match 0.1250
Find 0.1875
App vlookup 0.1093
WF vlookup 0.0937

It seems returning values via worksheet function.vlookup is faster than others with data in the attached file.

[mancubus]

For 150000 lookup values... (generated by copying the current 1500 cells down 99 times)

Code execution times in seconds
Match 10.9101
Find 19.3750
App vlookup 10.8554
WF vlookup 8.6093

again WorksheetFunction.VLookup seems faster than others.

if you insert VLOOKUP FORMULAS via macro it's even faster:
vlookup formula: 150000 formulas are inserted in B2:B150001 in 0.3007 seconds.

Sub test_vlookup_formula()
    With Worksheets("target")
        t = Timer
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        With .Range("B2:B" & LastRow)
            .Clear
            .Formula = "=VLookup(A2,Source!B:E, 4, 0)"
            '.Value = .Value 'this bit commented because it increased the time to 0,8320 from 0.3007 secs (0.5 secs more)
        End With
        .Cells(1, "K") = "VL_Formula"
        .Cells(2, "K") = Timer - t
    End With
End Sub