excel - passing range on another sheet to vlookup -


i can't reference range sheet function can =vlookup.

this works: =mvlookup(a2,b:c,2,",",", ")
isn't: =mvlookup(a2,sheet3!b:c,2,",",", ")

the code:

public function mvlookup(lookup_values, table_array range, col_index_num long, input_separator string, output_separator string) string   dim in0, out0,  in0 = split(lookup_values, input_separator)  redim out0(ubound(in0, 1))   = lbound(in0, 1) ubound(in0, 1)      out0(i) = application.worksheetfunction.vlookup(in0(i), table_array, col_index_num, false)   next   mvlookup = join(out0, output_separator)  end function

i don't know basic , i'm not planning learn it, use excel, sorry lame question. guess basic "basic" took me 30 minutes point reference(reading included), other 60 minutes in frustration because above problem.
me can go vba free life!

edit: although code above worked after excel restart, jeeped gave me safer solution , more universal functionality. that.

i not planning use on other strings addition, wrongly assumed there check data type every time , type passed along in background , vlookup acting accordingly. have learned how set default values function input variables.

see solution.

thanks again, jeeped!

you confusing 1 "1" , regardless of personal distaste vba, don't know of programming language treats them identical values (with possible exception of worksheet's countif function).

public function mvlookup(lookup_values, table_array range, col_index_num long, _                          optional input_separator string = ",", _                          optional output_separator string = ", ") string      dim in0 variant, out0 variant, long      in0 = split(lookup_values, input_separator)     redim out0(ubound(in0))      = lbound(in0) ubound(in0)         if isnumeric(in0(i))             if not iserror(application.match(val(in0(i)), application.index(table_array, 0, 1), 0)) _                 out0(i) = application.vlookup(val(in0(i)), table_array, col_index_num, false)         else             if not iserror(application.match(in0(i), application.index(table_array, 0, 1), 0)) _                 out0(i) = application.vlookup(in0(i), table_array, col_index_num, false)         end if      next      mvlookup = join(out0, output_separator)  end function

when split string variant array, end array of string elements. granted, look numbers not true numbers; merely textual representational facsimiles of true numbers. vlookup function not treat them numbers when first column in table_array parameter filled true numbers.

the isnumeric function can reconize string looks number , val function can convert text-that-looks-like-a-number true number.

i've added quick check ensure looking there before attempt stuff return value array.

your split strings one-dimensioned variant arrays. there no need supply rank in lbound / ubound functions.

    mvlookup_sample    mvlookup
            sample data on sheet3                                  results mvlookup


Comments

Post a Comment

Popular posts from this blog

javascript - jQuery: Add class depending on URL in the best way -

i have html progress meter: <div class="col-md-3" style="margin-left: -20px;"> <div class="progress-pos active" id="progess-1"> <div class="progress-pos-inner"> login </div> </div> </div> <div class="col-md-3 progress-pos-container"> <div class="progress-pos" id="progess-2"> <div class="progress-pos-inner"> scan items </div> </div> </div> <div class="col-md-3 progress-pos-container"> <div class="progress-pos" id="progess-3"> <div class="progress-pos-inner"> confirm address </div> </div> </div> <div class="col-md-3 progress-pos-container"> <div class="progress-pos" id="progess-4"> <div class="progress-pos-in...
Read more

caching - How to check if a url path exists in the service worker cache -

i need check if particular url path exists in service worker cache. example, suppose url is: /myserviceworker/service?a=110&b=70 this url exists in cache, there many of them different values of , b. now, suppose want refresh of these urls, how can that? i want know how access key values service worker cache. if know key, plan follows: var url = new url(key); if(url.pathname === "\/myserviceworker/service") refetch key but not sure how cache key , in format is. mean, string or url? cache api has match() method returns promise resolving in response object if match or undefined if no match exists. second parameter object can specify ignoresearch not take account url parameters. the ignoresearch option supported firefox ( chrome status here ). in other hand, retrieve cache entries, can use keys() method.
Read more

Redirect to a HTTPS version using .htaccess -

i need modify .htaccess file redirect urls https version without "www". http://example.com --> https://example.com http://www.example.com --> https://example.com https://www.example.com --> https://example.com this how .htaccess file looks like: rewriteengine on rewritecond %{request_filename} !-d rewritecond %{request_filename} !-f rewriterule ^(.*)$ redirect.php?id=$1&page=$2 [qsa,l] what modifications need make .htaccess in order forward https without "www"? it depends on doing current code. looks file called redirect trying display php page seo friendly url. think might confusing people. anyway, need , force https without www, need 1 more rule above. also second rule needs two capture groups in rewriterule test string because wanting 2 different values using $1 , $2 references. rewriteengine on #rediect www non www and/or http https --- combinations. rewritecond %{http_host} !^example\.com [nc,or] rewritecond %{https} !^...
Read more