Returning an address from an array
 

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!!!

Returning an address from an array
 

Consider VLOKUP():


=VLOOKUP(DATE(2007,5,12),A1:B5,2)
will yield 12 - the matching value in column B for 5/12
--
Gary''s Student - gsnu200718


"wienmichael" wrote:

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!!!

Returning an address from an array
 

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".


"Gary''s Student" wrote:

Consider VLOKUP():


=VLOOKUP(DATE(2007,5,12),A1:B5,2)
will yield 12 - the matching value in column B for 5/12
--
Gary''s Student - gsnu200718


"wienmichael" wrote:

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!!!

Returning an address from an array
 

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.

"Gary''s Student" wrote:

Consider VLOKUP():


=VLOOKUP(DATE(2007,5,12),A1:B5,2)
will yield 12 - the matching value in column B for 5/12
--
Gary''s Student - gsnu200718


"wienmichael" wrote:

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!!!

Returning an address from an array
 

=ADDRESS(MATCH(DATE(2007,5,12),A1:A5),2)

--
Gary''s Student - gsnu200718


"wienmichael" wrote:

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.

"Gary''s Student" wrote:

Consider VLOKUP():


=VLOOKUP(DATE(2007,5,12),A1:B5,2)
will yield 12 - the matching value in column B for 5/12
--
Gary''s Student - gsnu200718


"wienmichael" wrote:

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!!!

Returning an address from an array
 

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)):I NDEX(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))


"wienmichael" wrote:

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!!!

Returning an address from an array
 

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

"wienmichael" wrote in message
...
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!!!