top of page

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.


Sean O'Shea has more than 20 years of experience in the litigation support field with major law firms in New York and San Francisco.   He is an ACEDS Certified eDiscovery Specialist and a Relativity Certified Administrator.

​

The views expressed in this blog are those of the owner and do not reflect the views or opinions of the owner’s employer.

​

If you have a question or comment about this blog, please make a submission using the form to the right. 

Your details were sent successfully!

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

bottom of page