Returning an address from an array

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to return the address in an array for a value within that array.

Example

A B
1 5/10 7
2 5/11 10
3 5/12 12
4 5/13 9
5 5/14 15

I would like to return the address A3 when I look up the date 5/12 in the
array. Ultimately, I would like to manipulate the data in column B
corresponding to the row returned from looking up 5/12. For instance, I
would like to sum the numbers from B3 to B5. Or if I look up 5/11 to sum the
numbers from B2 to B4.

Help!!!
 
Consider VLOKUP():


=VLOOKUP(DATE(2007,5,12),A1:B5,2)
will yield 12 - the matching value in column B for 5/12
 
I want it to do the same thing as Vlookup, but instead of returning the value
12, I want it to return the address "B3".
 
I would like it to perform the same function as VLookup, but instead of
returning the value (12) in the cell B3, I want it to return the address B3.
 
How do you determine the ending cell in the range you want to sum?

Maybe you could use something like:

=SUM(INDEX(B1:B5,MATCH(DATE(2007,5,12),A1:A5,0)):INDEX(B1:B5,MATCH(DATE(2007,5,14),A1:A5,0)))

If the ending cell is always 2 below the first cell, maybe

=SUM(OFFSET(B1,MATCH(DATE(2007,5,12),A1:A5,0)-1,0,3,1))
 
Based on your explanation you want to sum 3 cells.

E1 = 5/12/2007

=SUM(OFFSET(B1,MATCH(E1,A1:A5,0)-1,,3))

Will sum B3:B5

The 3 in the formula is how many cells you want to sum.

Note that if you entered 5/14/2007 in cell E1 there is not any data below
5/14/2007 in your sample so the result would be 15, the sum of B5:B7.

Biff
 
Back
Top