Remember – MATCH returns the position of the matched value within the look-up_array, and not the actual value itself. We have also specified a match-type of “0” to indicate that we are interested in an exact match (1). In this example, we have told the MATCH formula to search for the value in cell B11, “Preferred”, out of a range of choices that are captured in the data series found in cells A5 thru A9. =Match(lookup_value,lookup_array,match_type)īelow is an example of the MATCH formula in Excel. The MATCH function returns the relative position of an item in an array, or series of data, that matches a specified value, in a specified order. This formula can be used in place of the commonly used HLOOKUP and VLOOKUP functions in financial modeling, and is most useful when looking at information related to things such as “comps” tables or sensitivity analysis. Today I would like to introduce you to two useful formulas, the “Index” and “Match” functions, which can actually be combined to create an even more useful “super” formula if you will.

In a prior post, I discussed scenario analysis using the OFFSET function.