When VLOOKUP won't work

If you've been using Excel for any length of time you've probably had occasion to use the VLOOKUP function.  This is a very useful function that allows you to look up a value in a range of data and return corresponding data from another column.  Unfortunately VLOOKUP will only return data from columns to the right of the look up column.  Here's a way to return data from a column to the left of the look up column.  To do this we'll use two functions, INDEX and MATCH.

First let's look at INDEX.  INDEX requires three arguments
=INDEX(data range, row number, column number).  In the table below our data range would be A1:F13.  If we entered =INDEX(A1:F13,7,4) the function would return My HD since that is the value in Row 7 and Column 4.  To become a useful function we need to be able to change the Row argument without typing in a different number.  To do this we use the MATCH function

The MATCH function also requires three arguments
=MATCH(value, single column data range, match type) where value is the value being searched for, data range is the column containing the value being searched for, match type is either 0 for exact match, 1 for less than or -1 for greater than.  In the above table =MATCH("BCM",C1:C13,0) would return 8.  This is the row number where BCM is found.

When we combine the two functions we can return data to either the left or the right of the search value.  If we enter =INDEX(A1:F13,MATCH("LMP",C1:C13,0),2) in cell J4 the function will return 201 which is the Branch Number for Leona.

One more step and we have a useful user function.  Instead of typing "LMP" in the formula we reference a cell, in this case we'll use J2.  Now we have the following formula:
=INDEX(A1:F13,MATCH(J2,C1:C13,0),2).  When the value in cell J2 is changed the Branch Number corresponding to that Sales ID will display.