Here is my final VLOOKUP function. As noted, it does not handle approximate matches since a) I'm not able to find a detailed specification of how to handle string comparisons in Excel and b) I don't need this functionality for myself currently.
/**
* VLOOKUP
* The VLOOKUP function searches for value in the leftmost column of lookup_array and returns the value in the same row based on the index_number.
* @param
lookup_value The value that you want to match in lookup_array
* @param
lookup_array The range of cells being searched
* @param
index_number The column number in table_array from which the matching value must be returned. The first column is 1.
* @param
not_exact_match Determines if you are looking for an exact match based on lookup_value.
* @return
mixed The value of the found cell
*/
public static function VLOOKUP($lookup_value, $lookup_array, $index_number, $not_exact_match=true) {
// index_number must be greater than or equal to 1
if ($index_number < 1) {
return self::$_errorCodes['value'];
}
// index_number must be less than or equal to the number of columns in lookup_array
if ($index_number > count($lookup_array)) {
return self::$_errorCodes['reference'];
}
// reindex lookup_array with numeric keys starting at 1
array_unshift($lookup_array, array());
$lookup_array = array_slice(array_values($lookup_array), 1, count($lookup_array), true);
// look for an exact match
$row_number = array_search($lookup_value, $lookup_array[1]);
// if an exact match is required, we have what we need to return an appropriate response
if ($not_exact_match == false) {
if ($row_number === false) {
return self::$_errorCodes['na'];
} else {
return $lookup_array[$index_number][$row_number];
}
}
// TODO: The VLOOKUP spec in Excel states that, at this point, we should search for
// the highest value that is less than lookup_value. However, documentation on how string
// values should be treated here is sparse.
return self::$_errorCodes['na'];
}
