Excel, Trying Index, Match, to lookup values with VBA -

i trying look-up values @ cell locations using vba, have searched google , on stackoverflow before asking because can't seem work.

the following code trying use, note budgetcode references cell containing 1 of codes in first column , mo references cell contains number (1-12) or contains short code (ytd, 1qtr, 2qtr, 3qtr). example want pull cad-ns february (2), , should 5666.40.

function ebudgetl(budgetcode string, mo string) ebudgetl = application.worksheetfunction.index(range("budget!g1:x5000"), _    application.worksheetfunction.match(budgetcode, range("budget!b1:b5000"), 0), _    application.worksheetfunction.match(mo, range("budget!g1:x1"), 0)) end function 

here part of data wish lookup:

                                       1    2          3       4 cad-ns        net sales           5264.0  5666.4  5614.9  5966.6 cosmat      e   material            6207.5  3660.0  3661.9  3560.9 cosdl       e   direct labor         610.4  105.3   167.1   123.6 cad-moil    e   indirect labor       671.2  163.4   181.6   161.7 cad-mosal   e   salary overhead      601.0  106.0   101.0   101.0 

here code in cell works, need in vba. (the reason need in vba budgetcode contain 2+ references separated comma , going use vba separate them , each independently.)

=index(budget!$g$1:$x$5000,match($f12,budget!$b$1:$b$5000,0),match(an$1,budget!$g$1:$x$1,0)) 

i appreciate much, have been @ 3 days now.

thanks,

enoch

the issue function parameter types.

when call function mo = cell an1, containing number 1, function type casts string "1", doen't exisit in range budget!$g$1:$x$1, since these numbers.

the solution use variant function paramters type.

to make debugging type of error easier, try not in single line of code. splitting line 2 x match functions , index, allow see second match return error.

couple of other points:

• if calling function udf (ie called worksheet cell), better not hard code ranges. written, calls ebudgetl not automatically recalculate when of data changes.
• the application object has version of index , match worksheetfunction calls not required

refactored version demonstrate:

function ebudgetl(rdata range, rbudcode range, rmo range, budgetcode variant, mo variant)     dim rw variant     dim col variant      application         col = .match(budgetcode, rbudcode, 0)         rw = .match(mo, rmo, 0)         ebudgetl = .index(rdata, col, rw)     end end function 

called as

=ebudgetl(budget!$g$1:$x$5000,budget!$b$1:$b$5000,budget!$g$1:$x$1,$f12,an$1)