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)
  • 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 '15 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 '15 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 '15 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 '15 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 '15 at 7:03
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.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.