Excel Performance - INDEX-MATCH combination -

i using excel create data sets used in vba application later. using formula:

=index(basedata!$l$2:$l$10000;match(dataset!d5&dataset!e5&dataset!k5;index(b‌​asedata!$b$2:$b$10000&basedata!$c$2:$c$10000&basedata!$d$2:$d$10000;0);0)) 

usually range f.ex.: a2 - a10000, because data can differently long , vary in data selection.

however, slows excel extremely down. switched manual calculations, then, when activating automatic again, excel instance takes extremely long , crashes.

i tried past data, when creating new dataset, have pull formula down again , through errors occur in data set.

any suggestions can make index-match formulas more performant?

i appreciate replies!

update

i guess lot of performance goes away because index-match not select exact range, counts in blank rows. how exactl range index match automatically?

as mention in comment above, long 'regular' formula , not array formula, may find success replacing "a1:a10000" "a:a". barring that, can create cell calculate reference number of rows of data have, , use cell indirectly reference complete column data in it.

calculating desired range

for following example work, assume that: column includes index key in form of numbers only; column includes no numbers in header , above; , index rows continuous, no breaks. start following formula:

 =count(a:a) 

if assumptions above hold, return number of data elements in table. once know data starts, can use count determine ends. assume header in row 2. (i include header if insert row beneath header, excel picks want include new row in formulas).with in mind, formula create excel-style reference finds last cell in column has data in it:

=address(row(a2)+1+count(a:a),column(a2),1,1) 

assuming 50 rows of data [which start @ row 3, below header], , other assumptions above, formula return text result "$a$53".

if wanted same thing, instead return full range in column data exists (from header row 53), follows:

=address(row(a2),column(a2),1,1)&":"&address(row(a2)+1+count(a:a),column(a2),1,1) 

this returns text string result "$a$2:$a$53", reference full index of unique id values. automatically move around expect if insert rows or columns. assume index want pull same data, column b, instead. formula same, except have "column(a2)" above, replace "column(b2)".

referencing calculated range

so have address of proper, limited columns - how reference areas in formula? using indirect function. indirect says "evaluate specific criteria. create cell reference. @ cell reference." in simplest form, this:

=indirect(a1) 

assume a1 holds value "b5". indirect pick value "b5", , instead of displaying "b5", go b5, , pick value there. use above, wrap whole thing in indirect function. instead of picking text string "$a$1:$a$53", reference range properly. so:

=indirect(address(row(a2),column(a2),1,1)&":"&address(row(a2)+1+count(a:a),column(a2),1,1)) 

using named range

but long formula have, , won't want use within specific cell simple index/match. instead of entering these formulas in cells (although could), recommend go formula ribbon -> name manager -> new name. call name index of "id_column". call name index of "b_column" (or more specific).

final formula

now, if wanted make index/match of table, automatically grows/shrinks change data, formula [this would, example, pick row column b column has number 100]:

=index(id_column,match(100, b_column, 0))