vlookup to return cell position as to where result was found

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" <[email protected]> wrote in message
news:[email protected]...
>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" <[email protected]> wrote in message
> news:[email protected]...
> >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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > >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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
news:[email protected]...
> =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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
> news:[email protected]...
> > =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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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
> > >
> > >
> > >
>
>
>