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 |
vlookup to return cell position as to where result was found
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 |
vlookup to return cell position as to where result was found
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 |
vlookup to return cell position as to where result was found
="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 |
vlookup to return cell position as to where result was found
=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 |
vlookup to return cell position as to where result was found
=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 |
vlookup to return cell position as to where result was found
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 |
vlookup to return cell position as to where result was found
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 |
All times are GMT +1. The time now is 10:22 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com