Formula to autocomplete entry matching two values in reference range

You can use VLOOKUP with the OFFSET and MATCH formulas to automatically complete entries in a third column, which have entries in columns A and B that match those used in the rows above, with the corresponding entries in column C above. So in this example when new entries are made for a product and its region, the name of the salesperson is automatically entered in column C, assuming all possible regions, products, and salespeople have been entered in the rows above.

=VLOOKUP(B9, OFFSET($B$2:$C$8, MATCH(A9,$A$2:$A$8,0)-1, 0, 2, 2), 2, 0)

Sort the entries used as the master reference in order by columns A and then B

So MATCH finds the row number of the value being searched for 'North' - 2 in this example. The -1 indicates its position in the designated range.

The OFFSET formula goes down the number of rows indicated by the MATCH formula and then across 0 columns and selects a range of two rows and two columns 2,2 . This version of the formula works with the 'reference range' if it only has two products for each region. If you change the array form: -1,0,2,2) TO: -1,0,3,3) you can have three products for each region.

VLOOKUP then searches the array for a given region and returns the salesperson for the listed product.

Thanks to Scott for posting this solution here.