Which is better? LOOKUP or INDEX?

P

PYO1012

I have data that's 50 some odd columns wide and about 500 rows deep. Is
there a more efficient method of looking up values using either a LOOKUP
function or an INDEX function? I'm asking because I notice Excel starts to
lag when I use VLOOKUP with such a large amount of data and I want to know if
it would be worth my time changing it to a INDEX/MATCH funtion. Does one or
the other work quicker?
 
T

T. Valko

LOOKUP would be the best *BUT* the data *MUST* be sorted in ascending order
and if there isn't an exact match LOOKUP will find the next lowest item.
This isn't optional like it is with VLOOKUP or INDEX/MATCH.

After LOOKUP, INDEX/MATCH would be the next best option.

Also note that if your data is sorted VLOOKUP can be a lot faster than when
the data is not sorted.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Lookup funtion: column index number (third parameter) 8
Better performance Vlookup or Index/Match? 1
Index function 3
INDEX / MATCH performance for lookups 4
lookup if? sumproduct if? index/match? 2
INDEX function array causes problem when attempting to use LARGEfunction to sort positive and negati 10
VLookup takes too long 4
lookup and return column number 2

Top