1

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)
9
  • Does your existing data account for all possible values for C, or could you encounter a combination where you would need to enter a new value for C?
    – fixer1234
    Jul 13, 2015 at 6:16
  • Hi Fixer, yeah sorry multiple edits occuring at once, I realised that I didn't have the range defined correctly in my example, but the fix is still returning #N/A. It doesn't need to have any error handling, any new country/product combination can be manually entered in. The Indirect Function hopefully adds this new value to the next time this occurrence happens. P.S. I'm also trying the formula as an Array
    – Stacker
    Jul 13, 2015 at 6:20
  • 1
    A much simpler approach: create a lookup table with two columns--A&B and C for the seven combinations. For each new row, lookup A&B.
    – fixer1234
    Jul 13, 2015 at 6:46
  • Hi Fixer, Appreciate your response and solution, though I'd rather figure out why my formula isn't working... Also, the example is a more simplified solution. I have multiple Products (4+) possible products per country (10+) making the combinations endless for a lookup table.
    – Stacker
    Jul 13, 2015 at 6:49
  • @Stacker: BTW, if you "address" your comment like this, the person will get a message flag, otherwise, nobody will be aware of your posting (needed if more than one person is associated with a question or answer). Suggestion: add a helper column with A&B (can be hidden). Then just do index/match on the rows above the current row.
    – fixer1234
    Jul 13, 2015 at 7:03

1 Answer 1

0

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.

2
  • Fantastic, thanks for the help and also explanation. Works really well I cannot thank you enough :)
    – Stacker
    Jul 15, 2015 at 22:21
  • My pleasure.  I apologize for taking so long to post the explanation. Jul 15, 2015 at 22:23

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .