Recently I've had to use formulas that perform left-sided lookups at work. The combination of Index and Match in a formula will do so, but I always seem to have a hard time remembering how to build the formula. Recently I put together this color-coded example to help:

The formula in cell C8 looks like this:

=INDEX(Range_of_Values_To_Look_Up,MATCH(Look_Up_Value,Range_of_Look_Up_Values,0),1)

or

=INDEX(B2:B6,MATCH(B8,D2:D6,0),1)

Very nice, but it’s too bad you can’t color-code the different parts of the formula as well, to match the key. Maybe something to suggest to MS for the next release?

Thx,
JP