I would like to run a vlookup for a name and have it return the location of
the cell where it finds it so I can use it in a sum(offset()) function.

column A
row 1 Sam
row 2 Bill
row 3 Eva


Vlookup( EVA,A1:C3,location Cell) would like result to be A3

--
Thanks

Roch

see your previous post for two answers

"Rock" wrote in message
...
I would like to run a vlookup for a name and have it return the location of
the cell where it finds it so I can use it in a sum(offset()) function.

column A
row 1 Sam
row 2 Bill
row 3 Eva


Vlookup( EVA,A1:C3,location Cell) would like result to be A3

--
Thanks

Roch

I'm sorry I still don't understand should the match go where I put "location
Cell"
Vlookup( EVA,A1:C3,location Cell) would like result to be A3 here

and can I incoporate it in the following function

=SUM(OFFSET(Vlookup( EVA,A1:C3,match(EVA,???? )),15,15,28,1))
{------------- result A3------------------}


Thanks
Roch

"JulieD" wrote:

see your previous post for two answers

"Rock" wrote in message
...
I would like to run a vlookup for a name and have it return the location of
the cell where it finds it so I can use it in a sum(offset()) function.

column A
row 1 Sam
row 2 Bill
row 3 Eva


Vlookup( EVA,A1:C3,location Cell) would like result to be A3

--
Thanks

Roch

=SUM(OFFSET(Indirect("A"&match("EVA",$A$1:$A:$3,0) ),15,15,28,1))

or much simpler

=SUM(OFFSET(A1,match("EVA",$A$1:$A:$3,0)+14,15,28, 1))



--
Regards,
Tom Ogilvy

"Rock" wrote in message
...
I'm sorry I still don't understand should the match go where I put
"location
Cell"
Vlookup( EVA,A1:C3,location Cell) would like result to be A3 here

and can I incoporate it in the following function

=SUM(OFFSET(Vlookup( EVA,A1:C3,match(EVA,???? )),15,15,28,1))
{------------- result A3------------------}


Thanks
Roch

"JulieD" wrote:

see your previous post for two answers

"Rock" wrote in message
...
I would like to run a vlookup for a name and have it return the
location of
the cell where it finds it so I can use it in a sum(offset())
function.

column A
row 1 Sam
row 2 Bill
row 3 Eva


Vlookup( EVA,A1:C3,location Cell) would like result to be A3

--
Thanks

Roch

="A"&match("Eva",A1:A3,0)

but that wouldn't be useful for the offset function

maybe something like
=sum(offset($A$1,match("Eva",$A$1:$A$3,0)-1,1,1,10))

would sum B3:K3

--
Regards,
Tom Ogilvy


"Rock" wrote in message
...
I would like to run a vlookup for a name and have it return the location
of
the cell where it finds it so I can use it in a sum(offset()) function.

column A
row 1 Sam
row 2 Bill
row 3 Eva


Vlookup( EVA,A1:C3,location Cell) would like result to be A3

--
Thanks

Roch

=sum(offset($A$1,match("Eva",$A$1:$A$3,0)-1,1,1,10))

This does not seem to work as it is suppose to.It makes sense when I look at
it but
it does not seem to go to $a$1then down to Eva -1 and when I change the name
it does not seem to move as it should. The ="A"&match one does give me the
right cell number but cannot be put into the sum offset formula????

Thanks

Roch




"Tom Ogilvy" wrote:

="A"&match("Eva",A1:A3,0)

but that wouldn't be useful for the offset function

maybe something like
=sum(offset($A$1,match("Eva",$A$1:$A$3,0)-1,1,1,10))

would sum B3:K3

--
Regards,
Tom Ogilvy


"Rock" wrote in message
...
I would like to run a vlookup for a name and have it return the location
of
the cell where it finds it so I can use it in a sum(offset()) function.

column A
row 1 Sam
row 2 Bill
row 3 Eva


Vlookup( EVA,A1:C3,location Cell) would like result to be A3

--
Thanks

Roch

formula was tested and works as described. It doesn't go to Eva - 1,
because offset is zero based. So it goes to the Eva row.


--
Regards,
Tom Ogilvy

"Rock" wrote in message
...
=sum(offset($A$1,match("Eva",$A$1:$A$3,0)-1,1,1,10))

This does not seem to work as it is suppose to.It makes sense when I look
at
it but
it does not seem to go to $a$1then down to Eva -1 and when I change the
name
it does not seem to move as it should. The ="A"&match one does give me the
right cell number but cannot be put into the sum offset formula????

Thanks

Roch




"Tom Ogilvy" wrote:

="A"&match("Eva",A1:A3,0)

but that wouldn't be useful for the offset function

maybe something like
=sum(offset($A$1,match("Eva",$A$1:$A$3,0)-1,1,1,10))

would sum B3:K3

--
Regards,
Tom Ogilvy


"Rock" wrote in message
...
I would like to run a vlookup for a name and have it return the
location
of
the cell where it finds it so I can use it in a sum(offset())
function.

column A
row 1 Sam
row 2 Bill
row 3 Eva


Vlookup( EVA,A1:C3,location Cell) would like result to be A3

--
Thanks

Roch

Yes, it does work thank you very much Tom. Much appreciated.

"Tom Ogilvy" wrote:

formula was tested and works as described. It doesn't go to Eva - 1,
because offset is zero based. So it goes to the Eva row.


--
Regards,
Tom Ogilvy

"Rock" wrote in message
...
=sum(offset($A$1,match("Eva",$A$1:$A$3,0)-1,1,1,10))

This does not seem to work as it is suppose to.It makes sense when I look
at
it but
it does not seem to go to $a$1then down to Eva -1 and when I change the
name
it does not seem to move as it should. The ="A"&match one does give me the
right cell number but cannot be put into the sum offset formula????

Thanks

Roch




"Tom Ogilvy" wrote:

="A"&match("Eva",A1:A3,0)

but that wouldn't be useful for the offset function

maybe something like
=sum(offset($A$1,match("Eva",$A$1:$A$3,0)-1,1,1,10))

would sum B3:K3

--
Regards,
Tom Ogilvy


"Rock" wrote in message
...
I would like to run a vlookup for a name and have it return the
location
of
the cell where it finds it so I can use it in a sum(offset())
function.

column A
row 1 Sam
row 2 Bill
row 3 Eva


Vlookup( EVA,A1:C3,location Cell) would like result to be A3

--
Thanks

Roch