Formula to autocomplete entry matching two values in reference range

# Formula to autocomplete entry matching two values in reference range

August 22, 2019

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.

Â

Â

Â

Â

Â

Â

Â