Why I like INDEX / MATCH combo (and not VLOOKUP)
In an earlier post I highlighted Daniel Ferry’s fantastic article on the function INDEX. INDEX in a sense is the simplest of formulas which masks its power. The syntax is:
=INDEX(array, row_num, [column_num])
Basically it’s saying from this array (or range of cells) return me this row and this column. Seems pretty boring at first as if you use index numbers for the nth row_num and co_num, why not just use =A1 ie the cell’s address. OK you could use cell references for the row and index parts and that might be more useful. But when you substitute the row number and/or the column number parts with other formulas like MATCH you can have a very powerful tool looking up rows and columns according to your criteria like this:
=INDEX(array, MATCH Formula to return a row no. matching your criteria, Index no for nth column)
=INDEX(array, Index no for nth row, MATCH Formula to return a column no. matching your criteria)
=INDEX(array, MATCH Formula return a row no. matching your criteria, MATCH Formula to return a column no. matching your criteria)
OK so how does the MATCH Formula work?
which in friendlier language means:
=MATCH(what are you looking for, from which vector i.e. 1 x col multi row range or multi col 1 row range, type of match exact or approximate). Note although Excel’s syntax says lookup_array, lookup_vector is more accurate and a vector is a one row range or one column range.
Put this together and you have a simple, once you are used to it, more reliable lookup machine. More reliable because at least for exact matches your data can be in any order (must be sorted for approximate matches) and you can look left and right. It took me a while for it to become second nature but now that it is it’s so easy to go to any sheet and return what I want reliably without having to arrange the sheet’s format or sort order.