Re: how do I use vlookup for multiple occurrences of the same value

Tech-Archive recommends: Speed Up your PC by fixing your registry

Harlan Grove wrote: 
Alan Beban wrote...
...

The particular formula you provided is slower than the array entered
VLookups formula when the number of recalculations on a *** gets
relatively large.


...

There are situations in which Excel workbooks can't use any VBA, so
it's good to know how to do certain tasks using no VBA. We may disagree
about this, but IMO it's best to avoid VBA for anything that can be
done compactly with built-in functions and defined names. Note the
fuzzy term 'compactly'.

On the other hand, if recalc performance is absolutely critical, better
to use 2 formulas/cells per each result plus one extra formula/cell.

G2:
=ROWS(Tbl)

H1:
=VLOOKUP(G$1,Tbl,2,0)

I1:
=MATCH(G$1,INDEX(Tbl,0,1),0)

H2:
=INDEX(Tbl,I2,2)

I2:
=MATCH(G$1,OFFSET(Tbl,I1,0,G$2-I1,1),0)

I guarantee you this will run recalc circles around your VLookups
formulas. Benchmark results available upon request.

Which of the formulas, if any, are to be array entered?

Which get copied where to display the output?

Alan Beban
.

Quantcast