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.

 

 

 

 

 

 

 

Please reload

Contact Me With Your Litigation Support Questions:

seankevinoshea@hotmail.com

  • Twitter Long Shadow

© 2015 by Sean O'Shea . Proudly created with Wix.com