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.
The spreadsheet is here (.ods)
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.