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

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

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

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

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

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