Excel – Auto-populate values based on multiple dependencies in Excel

microsoft excelmicrosoft-excel-2010worksheet-function

I have three columns with information filled in already. The value in Column C is dependent on two identifiers from Columns A & B (i.e., George is always the seller of Apples in America, but Sarah is the seller of Beets).

I have enough data in rows 1-8 that I want the cells from 9 onwards to start automatically filling the required information each time I input a new row.

Thus, if I enter in "Beets" in A9 and "Australia" in B9, then C9 should pull the value "Amy". I will pull this value down to autofill the rest of the table for future rows to be added automatically filling.

I used the INDIRECT(ROW-1) function to reference the whole column above the cell with the formula to get the correct data, if that makes sense…

       A         B         C
1   Product  Country    Contact 
2   Apple    Australia  John
3   Apple    America    George
4   Beets    Australia  Amy
5   Beets    America    Sarah
6   Carrot   Australia  Greg
7   Carrot   America    Belinda
8   Apple    Australia  John 
9   Beets    Australia  _Formula:_

=INDEX((INDIRECT("C2:C"&ROW()-1)),MATCH(A10,IF((INDIRECT("B2:B"&ROW()-1))=B10,(INDIRECT("A2:A"&ROW()-1)))),0)

Best Answer

Here's an answer that seems to work:

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

Working from the inside out:

  • VLOOKUP(B9, OFFSET(B$2:C$7, MATCH(A9,A$2:A$7,0) -1, 0, 2, 2), 2, 0)
    MATCH(A9, A$2:A$7, 0) finds A9 (Beets) in the range A2:A7 { Apple, Apple, Beets, Beets, Carrot, Carrot }, yielding an index value in the range 1-6.  Of course, because A2 = A3, A4 = A5, and A6 = A7, the index value will always be 1, 3, or 5; for Beets, it is 3.
  • MATCH(A9, A$2:A$7, 0)-1 remaps the above to be in the range 0-5 (specifically, 0, 2, or 4; for Beets, it is 2).
  • OFFSET(B$2:C$7, the_above, 0, 2, 2) says
    • take the B2:C7 region,
    • from the top left corner (B2), go down two rows (since the first occurrence of Beets in A2:A7 is in the third row, that means we must go down two (3−1) rows from row 2) and go right zero columns, arriving at cell B4,
    • and then take a 2×2 region starting from that point. which gets us to the range B4:C5, which is 
      Australia   Amy
America     Sarah
      which is the world-wide list of beets sellers.  Note that we want a region that’s two columns wide, because we want to associate countries with sellers, but the height should be the number of countries (which happens to be two in this example).  If there were seven countries, we would change this to OFFSET(B$2:C$7, MATCH(A9,A$2:A$7,0)-1, 0, 7, 2)
      (Although, if there were seven countries (and three commodities), the introductory block would be 21 rows high, so we’d be using ranges from row 2 to row 22.)
  • VLOOKUP(B9, OFFSET(B$2:C$7, MATCH(A9,A$2:A$7,0)-1, 0, 2, 2) , 2, 0)
    VLOOKUP (and its brothers, LOOKUP and HLOOKUP) are, essentially, shorthand for INDEX+MATCH.  This searches the first column (the V stands for vertical) of the beets sellers region/array (B4:C5) for B9 (Australia) and then returns the value from the 2nd column of the row that matched the lookup value (Australia).  Australia is in row 4.  Since we are talking about Columns B and C, Column C is “the 2nd column” in this context, so this gets us to cell C4, which contains Amy.

… which is the desired result for row 9, since Amy is the Australian beet vendor.

Related Question