PDA

View Full Version : [SOLVED] Find vs Match



kathyb0527
02-21-2014, 10:25 AM
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
02-21-2014, 09:56 PM
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 matchperhaps you should try both, see which is better /faster

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

Bob Phillips
02-22-2014, 04:16 AM
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
02-23-2014, 01:39 AM
Find looks for a particular value within the contents of a single celli assumed he was referring to excels find method, rather than formula

GTO
02-23-2014, 04:11 AM
i assumed he was referring to excels find method, rather than formula

Kathy...

Most likely she

Bob Phillips
02-23-2014, 05:34 AM
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
02-26-2014, 10:46 AM
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
02-26-2014, 03:38 PM
VLookUp function

mancubus
02-27-2014, 01:37 AM
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
02-27-2014, 02:34 AM
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