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
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.
Sadly, the use of the volatile INDIRECT and OFFSET are almost ubiquitously recommended in such cases on the various Excel sites around, even though there exists a perfectly good, minimally volatile (in fact, volatile "at workbook open" only, as I understand) set-up using INDEX.
To determine the last non-empty row in a column, column A say, it will be useful to know the datatype of the entries within that column, and also whether there are any null strings ("") present.
Although there are set-ups available which will work whatever the datatype of the entries in question, they are necessarily of a type such that they must process each element within the range passed to them. As such - and particularly if the range over which it is necessary to calculate is quite large - they can be extremely resource-heavy (the second suggestion by kyle being a good example, having to process more than a million cells, irrespective of whether the last non-empty cell is in row 1 or row 1048576).
It is also worthwhile storing the value of the last non-empty row as a Defined Name, LRow say.
The possible definitions for LRow are then as follows:
1) If there are no null strings and all entries are non-numerical, you can define LRow as:
=MATCH("Ω",A:A)
2) If there are no null strings and all entries are numerical, you can define LRow as:
=MATCH(9.9E+307,A:A)
3) If there are no null strings and entries are of a mixed datatype, i.e. some numerical, some non-numerical, you can define LRow as:
=MAX(MATCH("Ω",A:A),MATCH(9.9E+307,A:A))
If there are null strings within the range then the solutions become necessarily more complex, and it is no longer possible to avoid a construction in which each cell within the array is processed individually. As such, the best we can do is to minimize the range which we pass to be processed.
Hence, we have:
4) If there are null strings, you can define LRow as:
=MATCH(1,0/(LEN(A1:INDEX(A:A,MAX(MATCH("Ω",A:A),MATCH(9.9E+307,A:A))))>0))
(You could of course make this even more efficient if the datatype is consistently numerical or non-numerical, as above.)
which requires array-entry (CSE) and which, in general, will vastly reduce the range being processed, especially compared to something such as:
=MATCH(1,0/(LEN(A:A)>0))
which is disastrous in terms of efficiency.
Note that the additional functions within the former are of very little detriment: it is far more important to reduce the number of cells being processed than to worry about an additional function call or two.
You can now use this within an INDEX construction to dynamically define your range. For example:
=A1:INDEX(A:A,LRow)
which, as mentioned, is barely volatile at all.
Regards
Best Answer
To just clarify Mureinik's answer a little bit, because it's a good start, for column MyColumn you could use:
OFFSET($A$1,0,MyColumn-1,ROWS($A:$A),1)Start at $A$1, and then offset by 0 rows and (your column number minus 1), using a height of ROWS($A:$A) and a width of 1. Max rows in recent versions is 1,048,576. You can use a smaller height if you know what the maximum height is likely to be.
So to sum column 3:
The answer from sgp667 is not bad, but note that SUBSTITUTE could be a little messy and slow if you are using it a lot.
To reference another sheet, just change the reference to $A$1, so for instance:
SUM(OFFSET(Sheet2!$A$1,0,3-1,ROWS($A:$A),1))