string - Is there a an array equivalent to vlookup in vba? -

string - Is there a an array equivalent to vlookup in vba? -

my vba code bunch of big ranges uses worksheetfunction.vlookup find needed values. ranges can upwards of 25,000 cells, however, takes forever. there equivalent function arrays?

i've seen lots of answers seem address returning true/false in there exact string match. need string's location.

how ...

function myvlook(arg range, target range, colidx integer) range dim idx integer if arg = "" set myvlook = [paramnothing] else idx = 1 target.rows.count if target(idx, 1) = arg if colidx < 0 set myvlook = target(idx, 1).offset(0, colidx) else set myvlook = target(idx, colidx) end if exit end if next idx end if end function

[paramnothing] single cell range in worksheet containing application-specific text; otherwise works normal vlookup ... can specify negative column offsets though (something miss in regular vlookup), , didn't built in flag range searches.

arrays string excel-vba