Posted by admin on

VLOOKUP vs INDEX ( MATCH)

How awesome is this! Excel =INDEX, is a matrix retrieve i.e. =INDEX(array, row, col) retrieves an entry from the two dimensional array. If we combine this with MATCH which returns a row number we can use these functions instead of =VLOOKUP, its allegedly faster too. MATCH operates on a column or linguistically a list.
=INDEX(myarray, MATCH(querykey, querylist,matchkey), replycolumn)
will return from the replycolumn, the element corresponding to the querykey. The matching key is 0,1,2 for equality and less than, greater than.

This needs example uploads; for another day.
indexmatch-w542
The spreadsheet is here (.ods)

ooOOOoo

I was pointed at this, http://spreadsheeto.com/index-match/ by the author, exceedingly comprehensive.
If the source array is a table, the functions will prompt for the columns names.


This site uses Akismet to reduce spam. Learn how your comment data is processed.

By continuing to use the site, you agree to the use of cookies. more information

The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.

Close