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.

        
            ^{sample data on sheet3                                  results mvlookup}