Somewhere along the line, I realised I was better off using what I call the INDEX / MATCH combo in nearly all lookup situations and not the ever popular VLOOKUP. The INDEX /MATCH combo can look left and right (unlike VLOOKUP which can only look right), you don’t have to have sorted data to lookup exact matches (like fussy VLOOKUP) and because it’s part of INDEX, a non volatile formula, you know its a light footed formula. But one important point here, is even INDEX/MATCH needs sorted data for approximate matches, so watch out for this one.
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?
=MATCH(lookup_value, lookup_array,[match_type])
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.