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.

Leave a Reply

Your email address will not be published. Required fields are marked *

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