vlookup and offset
Hi, Can anyone PLEASE help me with the following? - it's driving me cRaZy!! I want to look up a value in column B and return the contents of the cell across 4 and down 4 from the value looked up in column B. Is this possible? Rachel ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/
RachelS.vyc4y
excel.misc
78881 articles. 
5 followers.
12 Replies
572 Views
"RachelS" <RachelS.vyc4y@excelforum-nospam.com> wrote in message news:RachelS.vyc4y@excelforum-nospam.com... > Hi, > > Can anyone PLEASE help me with the following? - it's driving me cRaZy!! > > > I want to look up a value in column B and return the contents of the > cell across 4 and down 4 from the value looked up in column B. Is this > possible? > > Rachel Two possibilities: =INDEX($F$5:$F$14,MATCH(A1,$B$1:$B$10,0)) In this equation, the row offset of 4 is done by making the range in column F start 4 rows below that in column B. =INDEX($F:$F,MATCH(A1,$B:$B,0)+4) In this equation, the row offset of 4 is added to the position found by the MATCH.
|
0 |
|
Paul
10/27/2003 10:44:38 AM
Hi When data in column B are in range B2:B100, and the value you are looking up is stored in cell X2, then: =OFFSET($B$1,MATCH($X$2,$B$2:$B100,0)+4,4) PS. VLOOKUP returns the value, not the reference to it, or the position of this value- so it's no help for you in this case. Arvi Laanemets "RachelS" <RachelS.vyc4y@excelforum-nospam.com> wrote in message news:RachelS.vyc4y@excelforum-nospam.com... > Hi, > > Can anyone PLEASE help me with the following? - it's driving me cRaZy!! > > > I want to look up a value in column B and return the contents of the > cell across 4 and down 4 from the value looked up in column B. Is this > possible? > > Rachel > > > > ------------------------------------------------ > ~~ Message posted from http://www.ExcelTip.com/ > ~~ View and post usenet messages directly from http://www.ExcelForum.com/ >
RemoveThis_arvilSo this cries for the question, what is the difference in using Index() and Offset()? TIA,, "Arvi Laanemets" <RemoveThis_arvil@tarkon.ee> wrote in message news:ea5CmlHnDHA.1656@tk2msftngp13.phx.gbl... > Hi > > When data in column B are in range B2:B100, and the value you are looking up > is stored in cell X2, then: > =OFFSET($B$1,MATCH($X$2,$B$2:$B100,0)+4,4) > > PS. VLOOKUP returns the value, not the reference to it, or the position of > this value- so it's no help for you in this case. > > > Arvi Laanemets > > > "RachelS" <RachelS.vyc4y@excelforum-nospam.com> wrote in message > news:RachelS.vyc4y@excelforum-nospam.com... > > Hi, > > > > Can anyone PLEASE help me with the following? - it's driving me cRaZy!! > > > > > > I want to look up a value in column B and return the contents of the > > cell across 4 and down 4 from the value looked up in column B. Is this > > possible? > > > > Rachel > > > > > > > > ------------------------------------------------ > > ~~ Message posted from http://www.ExcelTip.com/ > > ~~ View and post usenet messages directly from http://www.ExcelForum.com/ > > > >
jmayHave you looked up "Index" and "Offset" in Help? "JMay" <jmay@cox.net> wrote in message news:Yl8nb.735$Re.482@lakeread06... > So this cries for the question, what is the difference in using Index() and > Offset()? > TIA,, > > > "Arvi Laanemets" <RemoveThis_arvil@tarkon.ee> wrote in message > news:ea5CmlHnDHA.1656@tk2msftngp13.phx.gbl... > > Hi > > > > When data in column B are in range B2:B100, and the value you are looking > up > > is stored in cell X2, then: > > =OFFSET($B$1,MATCH($X$2,$B$2:$B100,0)+4,4) > > > > PS. VLOOKUP returns the value, not the reference to it, or the position of > > this value- so it's no help for you in this case. > > > > > > Arvi Laanemets > > > > > > "RachelS" <RachelS.vyc4y@excelforum-nospam.com> wrote in message > > news:RachelS.vyc4y@excelforum-nospam.com... > > > Hi, > > > > > > Can anyone PLEASE help me with the following? - it's driving me cRaZy!! > > > > > > > > > I want to look up a value in column B and return the contents of the > > > cell across 4 and down 4 from the value looked up in column B. Is this > > > possible? > > > > > > Rachel > > > > > > > > > > > > ------------------------------------------------ > > > ~~ Message posted from http://www.ExcelTip.com/ > > > ~~ View and post usenet messages directly from > http://www.ExcelForum.com/ > > > > > > > > >
|
|
0 |
|
Paul
10/27/2003 12:43:10 PM
Yes sir... "Paul" <none> wrote in message news:Ok6zofInDHA.424@TK2MSFTNGP10.phx.gbl... > Have you looked up "Index" and "Offset" in Help? > > "JMay" <jmay@cox.net> wrote in message news:Yl8nb.735$Re.482@lakeread06... > > So this cries for the question, what is the difference in using Index() > and > > Offset()? > > TIA,, > > > > > > "Arvi Laanemets" <RemoveThis_arvil@tarkon.ee> wrote in message > > news:ea5CmlHnDHA.1656@tk2msftngp13.phx.gbl... > > > Hi > > > > > > When data in column B are in range B2:B100, and the value you are > looking > > up > > > is stored in cell X2, then: > > > =OFFSET($B$1,MATCH($X$2,$B$2:$B100,0)+4,4) > > > > > > PS. VLOOKUP returns the value, not the reference to it, or the position > of > > > this value- so it's no help for you in this case. > > > > > > > > > Arvi Laanemets > > > > > > > > > "RachelS" <RachelS.vyc4y@excelforum-nospam.com> wrote in message > > > news:RachelS.vyc4y@excelforum-nospam.com... > > > > Hi, > > > > > > > > Can anyone PLEASE help me with the following? - it's driving me > cRaZy!! > > > > > > > > > > > > I want to look up a value in column B and return the contents of the > > > > cell across 4 and down 4 from the value looked up in column B. Is this > > > > possible? > > > > > > > > Rachel > > > > > > > > > > > > > > > > ------------------------------------------------ > > > > ~~ Message posted from http://www.ExcelTip.com/ > > > > ~~ View and post usenet messages directly from > > http://www.ExcelForum.com/ > > > > > > > > > > > > > > > >
OK, so what did you not understand? "JMay" <jmay@cox.net> wrote in message news:K99nb.871$Re.1@lakeread06... > Yes sir... > > "Paul" <none> wrote in message news:Ok6zofInDHA.424@TK2MSFTNGP10.phx.gbl... > > Have you looked up "Index" and "Offset" in Help? > > > > "JMay" <jmay@cox.net> wrote in message news:Yl8nb.735$Re.482@lakeread06... > > > So this cries for the question, what is the difference in using Index() > > and > > > Offset()? > > > TIA,, > > > > > > > > > "Arvi Laanemets" <RemoveThis_arvil@tarkon.ee> wrote in message > > > news:ea5CmlHnDHA.1656@tk2msftngp13.phx.gbl... > > > > Hi > > > > > > > > When data in column B are in range B2:B100, and the value you are > > looking > > > up > > > > is stored in cell X2, then: > > > > =OFFSET($B$1,MATCH($X$2,$B$2:$B100,0)+4,4) > > > > > > > > PS. VLOOKUP returns the value, not the reference to it, or the > position > > of > > > > this value- so it's no help for you in this case. > > > > > > > > > > > > Arvi Laanemets > > > > > > > > > > > > "RachelS" <RachelS.vyc4y@excelforum-nospam.com> wrote in message > > > > news:RachelS.vyc4y@excelforum-nospam.com... > > > > > Hi, > > > > > > > > > > Can anyone PLEASE help me with the following? - it's driving me > > cRaZy!! > > > > > > > > > > > > > > > I want to look up a value in column B and return the contents of the > > > > > cell across 4 and down 4 from the value looked up in column B. Is > this > > > > > possible? > > > > > > > > > > Rachel > > > > > > > > > > > > > > > > > > > > ------------------------------------------------ > > > > > ~~ Message posted from http://www.ExcelTip.com/ > > > > > ~~ View and post usenet messages directly from > > > http://www.ExcelForum.com/ > > > > > > > > > > > > > > > > > > > > > > > > >
|
|
0 |
|
Paul
10/27/2003 2:01:49 PM
Both say the function will return a "reference"; even here I'm a bit confused as to me it looks like the they are retuning "values in the referenced cell". "Paul" <none> wrote in message news:urjcnLJnDHA.3316@TK2MSFTNGP11.phx.gbl... > OK, so what did you not understand? > > "JMay" <jmay@cox.net> wrote in message news:K99nb.871$Re.1@lakeread06... > > Yes sir... > > > > "Paul" <none> wrote in message > news:Ok6zofInDHA.424@TK2MSFTNGP10.phx.gbl... > > > Have you looked up "Index" and "Offset" in Help? > > > > > > "JMay" <jmay@cox.net> wrote in message > news:Yl8nb.735$Re.482@lakeread06... > > > > So this cries for the question, what is the difference in using > Index() > > > and > > > > Offset()? > > > > TIA,, > > > > > > > > > > > > "Arvi Laanemets" <RemoveThis_arvil@tarkon.ee> wrote in message > > > > news:ea5CmlHnDHA.1656@tk2msftngp13.phx.gbl... > > > > > Hi > > > > > > > > > > When data in column B are in range B2:B100, and the value you are > > > looking > > > > up > > > > > is stored in cell X2, then: > > > > > =OFFSET($B$1,MATCH($X$2,$B$2:$B100,0)+4,4) > > > > > > > > > > PS. VLOOKUP returns the value, not the reference to it, or the > > position > > > of > > > > > this value- so it's no help for you in this case. > > > > > > > > > > > > > > > Arvi Laanemets > > > > > > > > > > > > > > > "RachelS" <RachelS.vyc4y@excelforum-nospam.com> wrote in message > > > > > news:RachelS.vyc4y@excelforum-nospam.com... > > > > > > Hi, > > > > > > > > > > > > Can anyone PLEASE help me with the following? - it's driving me > > > cRaZy!! > > > > > > > > > > > > > > > > > > I want to look up a value in column B and return the contents of > the > > > > > > cell across 4 and down 4 from the value looked up in column B. Is > > this > > > > > > possible? > > > > > > > > > > > > Rachel > > > > > > > > > > > > > > > > > > > > > > > > ------------------------------------------------ > > > > > > ~~ Message posted from http://www.ExcelTip.com/ > > > > > > ~~ View and post usenet messages directly from > > > > http://www.ExcelForum.com/ > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > >
You can't display a reference so the behavior is to have the returned reference diplay the value of that reference. -- Regards, Tom Ogilvy "JMay" <jmay@cox.net> wrote in message news:Y1anb.974$Re.962@lakeread06... > Both say the function will return a "reference"; even here I'm a bit > confused as to me it looks like the they are retuning "values in the > referenced cell". > > "Paul" <none> wrote in message news:urjcnLJnDHA.3316@TK2MSFTNGP11.phx.gbl... > > OK, so what did you not understand? > > > > "JMay" <jmay@cox.net> wrote in message news:K99nb.871$Re.1@lakeread06... > > > Yes sir... > > > > > > "Paul" <none> wrote in message > > news:Ok6zofInDHA.424@TK2MSFTNGP10.phx.gbl... > > > > Have you looked up "Index" and "Offset" in Help? > > > > > > > > "JMay" <jmay@cox.net> wrote in message > > news:Yl8nb.735$Re.482@lakeread06... > > > > > So this cries for the question, what is the difference in using > > Index() > > > > and > > > > > Offset()? > > > > > TIA,, > > > > > > > > > > > > > > > "Arvi Laanemets" <RemoveThis_arvil@tarkon.ee> wrote in message > > > > > news:ea5CmlHnDHA.1656@tk2msftngp13.phx.gbl... > > > > > > Hi > > > > > > > > > > > > When data in column B are in range B2:B100, and the value you are > > > > looking > > > > > up > > > > > > is stored in cell X2, then: > > > > > > =OFFSET($B$1,MATCH($X$2,$B$2:$B100,0)+4,4) > > > > > > > > > > > > PS. VLOOKUP returns the value, not the reference to it, or the > > > position > > > > of > > > > > > this value- so it's no help for you in this case. > > > > > > > > > > > > > > > > > > Arvi Laanemets > > > > > > > > > > > > > > > > > > "RachelS" <RachelS.vyc4y@excelforum-nospam.com> wrote in message > > > > > > news:RachelS.vyc4y@excelforum-nospam.com... > > > > > > > Hi, > > > > > > > > > > > > > > Can anyone PLEASE help me with the following? - it's driving me > > > > cRaZy!! > > > > > > > > > > > > > > > > > > > > > I want to look up a value in column B and return the contents of > > the > > > > > > > cell across 4 and down 4 from the value looked up in column B. > Is > > > this > > > > > > > possible? > > > > > > > > > > > > > > Rachel > > > > > > > > > > > > > > > > > > > > > > > > > > > > ------------------------------------------------ > > > > > > > ~~ Message posted from http://www.ExcelTip.com/ > > > > > > > ~~ View and post usenet messages directly from > > > > > http://www.ExcelForum.com/ > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > >
twogilvyYes, I quite understand this confusion - I was confused for a long time about this. To expand a little on Tom Ogilvy's reply, a function is used as PART of a formula. For a function (such as INDEX or OFFSET) that returns a reference, it returns it to the formula, not to the cell. The behaviour is just as it would be if you replaced the function by that reference. For example, consider the case of a formula containing a function that returns a reference to a single cell: =OFFSET($A$1,3,0) This is equivalent to =A4 so you see what you would have done had you used the formula =A4 (that is, you see the contents of A4). It is easier to understand (at least, I found it so) in the case of a function that returns a reference to a range of cells. So, for example, in the formula =SUM(OFFSET($A$1,3,0,4)) the OFFSET function returns a reference to the range A4:A7, so the formula is equivalent to =SUM(A4:A7) which is easy to understand. "JMay" <jmay@cox.net> wrote in message news:Y1anb.974$Re.962@lakeread06... > Both say the function will return a "reference"; even here I'm a bit > confused as to me it looks like the they are retuning "values in the > referenced cell". > > "Paul" <none> wrote in message news:urjcnLJnDHA.3316@TK2MSFTNGP11.phx.gbl... > > OK, so what did you not understand? > > > > "JMay" <jmay@cox.net> wrote in message news:K99nb.871$Re.1@lakeread06... > > > Yes sir... > > > > > > "Paul" <none> wrote in message > > news:Ok6zofInDHA.424@TK2MSFTNGP10.phx.gbl... > > > > Have you looked up "Index" and "Offset" in Help? > > > > > > > > "JMay" <jmay@cox.net> wrote in message > > news:Yl8nb.735$Re.482@lakeread06... > > > > > So this cries for the question, what is the difference in using > > Index() > > > > and > > > > > Offset()? > > > > > TIA,, > > > > > > > > > > > > > > > "Arvi Laanemets" <RemoveThis_arvil@tarkon.ee> wrote in message > > > > > news:ea5CmlHnDHA.1656@tk2msftngp13.phx.gbl... > > > > > > Hi > > > > > > > > > > > > When data in column B are in range B2:B100, and the value you are > > > > looking > > > > > up > > > > > > is stored in cell X2, then: > > > > > > =OFFSET($B$1,MATCH($X$2,$B$2:$B100,0)+4,4) > > > > > > > > > > > > PS. VLOOKUP returns the value, not the reference to it, or the > > > position > > > > of > > > > > > this value- so it's no help for you in this case. > > > > > > > > > > > > > > > > > > Arvi Laanemets > > > > > > > > > > > > > > > > > > "RachelS" <RachelS.vyc4y@excelforum-nospam.com> wrote in message > > > > > > news:RachelS.vyc4y@excelforum-nospam.com... > > > > > > > Hi, > > > > > > > > > > > > > > Can anyone PLEASE help me with the following? - it's driving me > > > > cRaZy!! > > > > > > > > > > > > > > > > > > > > > I want to look up a value in column B and return the contents of > > the > > > > > > > cell across 4 and down 4 from the value looked up in column B. > Is > > > this > > > > > > > possible? > > > > > > > > > > > > > > Rachel > > > > > > > > > > > > > > > > > > > > > > > > > > > > ------------------------------------------------ > > > > > > > ~~ Message posted from http://www.ExcelTip.com/ > > > > > > > ~~ View and post usenet messages directly from > > > > > http://www.ExcelForum.com/ > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > >
|
|
0 |
|
Paul
10/27/2003 3:55:14 PM
Thanks Paul - I appreciate the time you took to give me the various examples, especially the last one - which "synched-it" for me; I now have a better understanding of how things work;;;;; JMay "Paul" <none> wrote in message news:#YdyGLKnDHA.1408@TK2MSFTNGP11.phx.gbl... > Yes, I quite understand this confusion - I was confused for a long time > about this. To expand a little on Tom Ogilvy's reply, a function is used as > PART of a formula. For a function (such as INDEX or OFFSET) that returns a > reference, it returns it to the formula, not to the cell. The behaviour is > just as it would be if you replaced the function by that reference. > > For example, consider the case of a formula containing a function that > returns a reference to a single cell: > =OFFSET($A$1,3,0) > This is equivalent to > =A4 > so you see what you would have done had you used the formula =A4 (that is, > you see the contents of A4). > > It is easier to understand (at least, I found it so) in the case of a > function that returns a reference to a range of cells. So, for example, in > the formula > =SUM(OFFSET($A$1,3,0,4)) > the OFFSET function returns a reference to the range A4:A7, so the formula > is equivalent to > =SUM(A4:A7) > which is easy to understand. > > "JMay" <jmay@cox.net> wrote in message news:Y1anb.974$Re.962@lakeread06... > > Both say the function will return a "reference"; even here I'm a bit > > confused as to me it looks like the they are retuning "values in the > > referenced cell". > > > > "Paul" <none> wrote in message > news:urjcnLJnDHA.3316@TK2MSFTNGP11.phx.gbl... > > > OK, so what did you not understand? > > > > > > "JMay" <jmay@cox.net> wrote in message news:K99nb.871$Re.1@lakeread06... > > > > Yes sir... > > > > > > > > "Paul" <none> wrote in message > > > news:Ok6zofInDHA.424@TK2MSFTNGP10.phx.gbl... > > > > > Have you looked up "Index" and "Offset" in Help? > > > > > > > > > > "JMay" <jmay@cox.net> wrote in message > > > news:Yl8nb.735$Re.482@lakeread06... > > > > > > So this cries for the question, what is the difference in using > > > Index() > > > > > and > > > > > > Offset()? > > > > > > TIA,, > > > > > > > > > > > > > > > > > > "Arvi Laanemets" <RemoveThis_arvil@tarkon.ee> wrote in message > > > > > > news:ea5CmlHnDHA.1656@tk2msftngp13.phx.gbl... > > > > > > > Hi > > > > > > > > > > > > > > When data in column B are in range B2:B100, and the value you > are > > > > > looking > > > > > > up > > > > > > > is stored in cell X2, then: > > > > > > > =OFFSET($B$1,MATCH($X$2,$B$2:$B100,0)+4,4) > > > > > > > > > > > > > > PS. VLOOKUP returns the value, not the reference to it, or the > > > > position > > > > > of > > > > > > > this value- so it's no help for you in this case. > > > > > > > > > > > > > > > > > > > > > Arvi Laanemets > > > > > > > > > > > > > > > > > > > > > "RachelS" <RachelS.vyc4y@excelforum-nospam.com> wrote in message > > > > > > > news:RachelS.vyc4y@excelforum-nospam.com... > > > > > > > > Hi, > > > > > > > > > > > > > > > > Can anyone PLEASE help me with the following? - it's driving > me > > > > > cRaZy!! > > > > > > > > > > > > > > > > > > > > > > > > I want to look up a value in column B and return the contents > of > > > the > > > > > > > > cell across 4 and down 4 from the value looked up in column B. > > Is > > > > this > > > > > > > > possible? > > > > > > > > > > > > > > > > Rachel > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > ------------------------------------------------ > > > > > > > > ~~ Message posted from http://www.ExcelTip.com/ > > > > > > > > ~~ View and post usenet messages directly from > > > > > > http://www.ExcelForum.com/ > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > >
Hi I read your's with Paul conversation here, and I think I have to add something. For most cases, the difference is like the color of cat - it can be anything, until she can catch the mouse. Usually everone uses this one, with which he is more confident. I myself started to use OFFSET, and I use INDEX only occasionally now. When you look more closely to syntax of both, then with OFFSET you set the anchor cell, and move the returned reference relatively to it - up or down, to left or to right. The only limit is, that the returned reference must be in limits of sheet. With INDEX, you set the range with all possible return values, and the returned reference must be in this range. Maybe the only difference occurs, when the reference to multi-cell range is returned. With INDEX(), you can return the reference to specific row or column in range (when I'm not completly wrong with my understanding of it's syntax). With OFFSET, you can return the reference to any rectangle on worksheet. Arvi Laanemets "JMay" <jmay@cox.net> wrote in message news:Yl8nb.735$Re.482@lakeread06... > So this cries for the question, what is the difference in using Index() and > Offset()? > TIA,, > > > "Arvi Laanemets" <RemoveThis_arvil@tarkon.ee> wrote in message > news:ea5CmlHnDHA.1656@tk2msftngp13.phx.gbl... > > Hi > > > > When data in column B are in range B2:B100, and the value you are looking > up > > is stored in cell X2, then: > > =OFFSET($B$1,MATCH($X$2,$B$2:$B100,0)+4,4) > > > > PS. VLOOKUP returns the value, not the reference to it, or the position of > > this value- so it's no help for you in this case. > > > > > > Arvi Laanemets > > > > > > "RachelS" <RachelS.vyc4y@excelforum-nospam.com> wrote in message > > news:RachelS.vyc4y@excelforum-nospam.com... > > > Hi, > > > > > > Can anyone PLEASE help me with the following? - it's driving me cRaZy!! > > > > > > > > > I want to look up a value in column B and return the contents of the > > > cell across 4 and down 4 from the value looked up in column B. Is this > > > possible? > > > > > > Rachel > > > > > > > > > > > > ------------------------------------------------ > > > ~~ Message posted from http://www.ExcelTip.com/ > > > ~~ View and post usenet messages directly from > http://www.ExcelForum.com/ > > > > > > > > >
Thanks Arvi, I understand even more now, thanks to your comments. JMay "Arvi Laanemets" <RemoveThis_arvil@tarkon.ee> wrote in message news:uy#lVvRnDHA.2188@TK2MSFTNGP11.phx.gbl... > Hi > > I read your's with Paul conversation here, and I think I have to add > something. > > For most cases, the difference is like the color of cat - it can be > anything, until she can catch the mouse. Usually everone uses this one, with > which he is more confident. I myself started to use OFFSET, and I use INDEX > only occasionally now. > > When you look more closely to syntax of both, then with OFFSET you set the > anchor cell, and move the returned reference relatively to it - up or down, > to left or to right. The only limit is, that the returned reference must be > in limits of sheet. With INDEX, you set the range with all possible return > values, and the returned reference must be in this range. > Maybe the only difference occurs, when the reference to multi-cell range is > returned. With INDEX(), you can return the reference to specific row or > column in range (when I'm not completly wrong with my understanding of it's > syntax). With OFFSET, you can return the reference to any rectangle on > worksheet. > > > Arvi Laanemets > > > "JMay" <jmay@cox.net> wrote in message news:Yl8nb.735$Re.482@lakeread06... > > So this cries for the question, what is the difference in using Index() > and > > Offset()? > > TIA,, > > > > > > "Arvi Laanemets" <RemoveThis_arvil@tarkon.ee> wrote in message > > news:ea5CmlHnDHA.1656@tk2msftngp13.phx.gbl... > > > Hi > > > > > > When data in column B are in range B2:B100, and the value you are > looking > > up > > > is stored in cell X2, then: > > > =OFFSET($B$1,MATCH($X$2,$B$2:$B100,0)+4,4) > > > > > > PS. VLOOKUP returns the value, not the reference to it, or the position > of > > > this value- so it's no help for you in this case. > > > > > > > > > Arvi Laanemets > > > > > > > > > "RachelS" <RachelS.vyc4y@excelforum-nospam.com> wrote in message > > > news:RachelS.vyc4y@excelforum-nospam.com... > > > > Hi, > > > > > > > > Can anyone PLEASE help me with the following? - it's driving me > cRaZy!! > > > > > > > > > > > > I want to look up a value in column B and return the contents of the > > > > cell across 4 and down 4 from the value looked up in column B. Is this > > > > possible? > > > > > > > > Rachel > > > > > > > > > > > > > > > > ------------------------------------------------ > > > > ~~ Message posted from http://www.ExcelTip.com/ > > > > ~~ View and post usenet messages directly from > > http://www.ExcelForum.com/ > > > > > > > > > > > > > > > >
Similar Artilces:
vlookup and Access Database Table
Can vlookup be used to get a result from an Access Database Table? Thanks. The vlookup function itself is an Excel function and as such can only retrieve data from an excel sheet. However you may be able to use the import external data menu option in the Data menu to retrieve your data from the access database, pull it into your excel workbook and then use the vlookup function. I never tried this myself, but maybe the idea can be applied. -- Jeroen van Nieuwkerk "finster26" wrote: > Can vlookup be used to get a result from an Access Database Table? > > Thanks....
Exporting data using Pulldown List & VLOOKUP
Hi Friends, I am developing a portfolio tracker excel file. I am having some difficulty in implementing one function, which I think I cannot create using macro. I am downloading various parameters using the SMF Add-In. Which is working fine. After downloading these data I want to export them into the other sheet "Pull Down Option". I tried to use OFFSET and VLOOKUP function. But it is not working. In the Pull Down Option sheet, I have created a droop down list, so when I change the droop down list, the data associated (Sheet Data) with it & a stock symbol shou...
VLookup in Excel 2007
In Excel 2003, when you looked up in the Criteria table, the identifier you chose had to be on the end and in alpha numeric order. Is this still the case? If I was to vlook up in multible tables and generate my own unique information, Can I go to a table created in the vlookup and use it even if it is not in alpha numeric order? Is there a work around? I don't really understand your question, but the quick answer is that there is no change in Vlookup from 2003 to 2007. You have always been able to use a table that wasn't in order by using False as the fourth parameter....
VLOOKUP and Ranges
I wonder if you can help me. I am trying to use VLOOKUP to do the following: - I have a two column table, the first has words in it and the second has percentages. - What I want to do is lookup values (from a variety of different cells) and match them to the percentages column - but bring back the words in the first column. The problem is that the values will not be an exact match - as basically the percentages column is a list to define the ranges (i.e. it would say in the first column a quarter and then the second column would say 24%-26%). What I have done is entered "a quart...
Charting with missing data or empty strings from vlookup()
I've used the vlookup() trick -- if(isna(vlookup(...)), "", vlookup(...)) to construct an intermediate table, which I would like to chart. However, charting cells which contain the empty string ("") works porly. I really don't want to treat those missing values as zeros, if there's any way to avoid it -- I just want the chart to treat them as missing. How can I change either the way I'm using vlookup() or the way I'm charting to allow me to chart this data with missing values? Thanks for your help, Mike Ironically, if you'd left the #N/A in t...
Vlookup, COUNTIF, IF or Nested
Hello, I have a work sheet "Raw Data" of about 1500 rows and 30 Columns. In columns K, K, L, M, T and W are names of staff. In worksheet "Lookups" I have a defined list of "Leavers". I need to return the names of the leaver/s in the last column of the "Raw Data" sheet. eg: Row 15 Column K contains "Joe Blog" Column M contains "Micky Mouse" and Column W contains "Jane Doe". in my defined list of leavers "Joe Blog" and "Jane Doe" are listed, in the last column of the "Raw Data&quo...
Offset 2nd ref.
A B ONE 1 TWO 2 THREE 3 ONE 4 Is there a way I can use the offset to look up a duplicate reference? To where I ref ONE and come up with 4. Try the below array formula which will lookup and return the 2nd matching value =INDEX(B1:B10,SMALL(IF(A1:A10="One",ROW(A1:A10)),2)) With text 'One" in cell C1 and the instance number in cell D1 (array entered using Ctrl+Shift+Enter instead of Enter) =INDEX(B1:B10,SMALL(IF(A1:A10=C1,ROW(A1:A10)),D1)) will return the last matching value in B if data is continuous (just works for...
Max Offset
Hi, I've got a range of numbers, J5:J28. I can get the maximum value using: =max(j5:j28) However, I want to find that max number and find the offset value 1 column to the left. Is this possible within a formula? I've tried all sorts of combos with MAX and OFFSET but can't get this one. Any help would be appreciated. Thanks, Dan. -- Voodoodan ------------------------------------------------------------------------ Voodoodan's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=597 View this thread: http://www.excelforum.com/showthread.php?threadid...
vlookup problem #12
Excel 2002 SP3 Win XP HE SP1 *Follow-up group is: microsoft.public.excel* *only reply to: microsoft.public.excel* hi, i am having a problem with vlookup wherein if i use formula A: =VLOOKUP(A2,NJ!$C$2:$C$48,1, FALSE) it works but formula B doesn't: =VLOOKUP(A2,NJ!$A$2:$E$48,5,FALSE) the only difference is the table array range which in the formula B looks into the same sheet etc., but at more columns. i can't understand what is causing the problem. i believe that the columns do not need to be sorted (help file); the sheets are formatted properly, etc. i've done LOTS of vloo...
vlookup help #2
hi i am providing you with my problem with attached file. i will be very grateful to you if you can solve my probem Attachment filename: help me.xls Download attachment: http://www.excelforum.com/attachment.php?postid=46482 -- Message posted from http://www.ExcelForum.com Hi first: you should try to explain your problem in plain text. Most people won't open attachments :-) To your problems: Just enter the 4th parameter of the VLOOKUP function to look for exact matches. So change =VLOOKUP(A17,A25:C27,3) to =VLOOKUP(A17,A25:C27,3,0) and =VLOOKUP(A17,A25:C2...
Exponential trendline offset
I have set the normal exponential trendline to a scatterchart but have to add an offset to the equation (instead of y = a * e^bx, I would like to use y=(a*e^bx) + c. How can I do that in Excel? Thanks in advance. Juliano Fernandes -- drjulianof ------------------------------------------------------------------------ drjulianof's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=30685 View this thread: http://www.excelforum.com/showthread.php?threadid=503477 If I want to fit to y=(a*e^bx) + c, I would use Solver If interested sent message to my private email w...
IF & Vlookup Assistance
Evening Chuck I tried your suggestion =IF(ISNA(VLOOKUP(A9,'Schedule'!$B$2:$C$20882,2,FALSE)),"Invalid Number"&","&VLOOKUP(LEFT(A9,4),$A$2:$C$20882,2,FALSE),VLOOKUP(A9,'Schedule'!$B$2:$C$20882,2,FALSE)) I got a messg #N/A. I tried it in a smaller size worksheet. example Sheet 1 is where I am doing the Vlookup from. Sheet 1 has 3 column with data col A contain both 8 digits and 4 digit user Id Col B contains the location and Col C contains the terminal ID. Sheet 2 is where I have the formulas doing the IF & Vlookup from. Col A is where I ...
VLOOKUP/HLOOKUP Question
Hi, I am trying to get Excel to look up a certain value from a small tabl of values and have tried using the VLOOKUP/HLOOKUP functions but can' get them to do exactly what I want. Here is how the spreadsheet looks: Recommended Tank Size: 4400 litres Range of Tank Sizes Available (litres): 1000, 2000, 3000, 4000, 5000, 6000, 7000, 8000, 9000, 10000 Basically their is an algorithm which calculates the "Recommended Tan Size" and then Excel is supposed to look up the nearest but nex -largest- value from the "Range of Tank Sizes Available" table. The problem I am havin...
Vlookup and multiple data
this is in my 1st work sheet (a1) 500 (b1)Bob (c1)Yes (d1)hants (A2) 500 (b2)Dave (c2)No (d2)surrey (A3) 500 (b3)Rob (c3)Yes (d3)york this is the formula i am using in the next worksheet for a drop down list =IF(VLOOKUP(C8,PowerGate!A3:$L$100,3,FALSE)=0,"",VLOOKUP(C8,PowerGate!A3:$L$100,3,FALSE)) It will show the 500 seperately but what i would like to do is when you select the 500 is to have all of the information for 500 shown in the next sheet.I don't want to have to select it individually. Hope this makes sense. Do you want the data for all rows wit...
Getting the next higer value with Vlookup
In VLookup / Hlookup function the True parameter gives the closest match with the search value but less than the search value.... i want to get the next greatest values of the Search Value.... how to do it.... Example ..... A B C 5 10 15 6 11 16 9 14 19 If i use =Vlookup(7,[Array],2,0)/ =Vlookup(7,Array],2,True) .... the answer is get is 11 BUT how to get it as 14!!!??? Please reply me at : getu32@gmail.com Hi One way =INDEX(B2:B4,MATCH(7,A2:A4,1)+1) -- Regards Roger Govier "getu32" <getu32@gmail.com> wrote in me...
How to use vlookup in an array formula
In sheet1, I have rows and columns whose cells contain strings like "S", "M", "L", and "XL". Some cells are empty. In sheet2, is a column A of the same strings and a column B of corresponding values, e.g. 1 corresponds to "S", 4 corresponds to "M", etc. I want to be able to sum the rows and columns in sheet1 based on the table in sheet2. The array in sheet1 is not the same size as the value column in sheet2. I tried to use an array formula like {sum(vlookup(sheet1!a1:a20,sheet2!a1:b4,FALSE))} but it doesn't seem to...
Offset, Dynamic range, Countif
I need to count the # specific items within a list of data (in Column K). Column K data is limited to four "Status" responses ("Confirmed" "Good" "Bad" "Non-Responsive"). The corresponding dates (in Column J) tells me the date these items were entered into system. I would like to have a formula which looks at the date within cell A3 (a variable input cell allowing me to search using a specific date) and looks only within the date range under column J and counts the # of "Non-Responsive" cells under column K (still abiding by th...
vlookup?? #2
I'm not sure how to do this but this is what I want to do. A B 6/27/05 262A029 6/23/05 267A042 6/24/05 267A043 6/24/05 271A001 I want to search column A for a certain date and pull out the data on column B but some dates have multiple matches on column B. I need all the column B data that matches the date. So in the example I have...When I search for 6/24/05 in column A I get "267A043" and "271A001" from column B. Can someone please help me!!! Thanks -- cutsygurl -----------------------------------------------------------------...
Dynamic Chart (OFFSET Function) plots empty cells.
I have read the posts about Excel charts plotting cells that are empty. I'm not sure I understood what I read, and our particular circumstances may be a bit different. (I'm not sure.) Anyway. Our problem is: We use Excel 2003 MSQuery to extract the results of an Access query and automatically put them in an Excel spreadsheet. Depending on the circumstances, the query doesn't always return the same amount of data. In order to automatically expand or shrink the range of cells that actually contain data, we use the OFFSET function DonationsTotals=OFFSET(Donations!$K$2,0,0,COUNTA(...
Preventing hyperlinks and vlookups from breaking
We are planning a re-organize our primary Drive and will be moving most of our folders and subfolders. We commonly use hyperlinks between workbooks. We are an Excel dependent department and have hundreds of files within our primary folders and subfolders. During testing, I find that when the source workbook is moved, the hyperlink in the dependent workbook no longer works and I get: "Cannot open specified file." I need to move the folders and subfolders soon and need to know how to prevent this error from occurring when the users try to use the hyperlinks. Als...
convar and offset queston
Hi all I try to do convar(A1:$A$10,$B$1:B10) convar(A2:$A$10,$B$1:B9) convar(A3:$A$10,$B$1:B8) convar(A4:$A$10,$B$1:B7) ..... convar(A10:$A$10,$B$1:B1) Getting the first argument (A1:A10) -> (A10:A10) with problems but how to get (B1:B10) -> (B1:B1) I tried to use Offset to decrement the ending cell without any success. Some knows how to do it? Cheers Wing GMB wrote: > Hi all > > I try to do > > convar(A1:$A$10,$B$1:B10) > convar(A2:$A$10,$B$1:B9) > convar(A3:$A$10,$B$1:B8) > convar(A4:$A$10,$B$1:B7) > .... > convar(A10:$A$10,$B$1:B1) > >...
Limit to nested Vlookups
Is there a limit to the number of nested Vlookups you can have -- karambo ----------------------------------------------------------------------- karambos's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1626 View this thread: http://www.excelforum.com/showthread.php?threadid=39940 There's a limit of 7 nested levels (not just for =vlookup()). karambos wrote: > > Is there a limit to the number of nested Vlookups you can have? > > -- > karambos > ------------------------------------------------------------------------ > karambos's ...
What cell is vlookup returning
Can anyone tell me what cell a vlookup is returning? Example if the vlookup function returns the value 100, what cell did that value of 100 come from. I have tried CELL("address",VLOOKUP(E5,G:J,4,FALSE)) but it does not work? Sorry for so many questions today. Thanks! ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ Try =CELL("address",INDEX(J1:J1000,MATCH(E5,G1:G1000,0))) -- Regards, Peo Sjoblom "jpx" <jpx.uxu8z@excelforum-n...
Vlookup & relative cell reference?
Hello-- I have a formula entered as follows: '=VLOOKUP(A8,Sheet1!$B$22:$O$22,2,FALSE) My Sheet 1 - match column is this: Row Column B 22 Store 1 - Primary 23 Store 1 - Secondary I'd like to automatically have Vlookup return the values from Sheet 1, in Row 23 without having to type that label in my original sheet, cell A9. Is there any quick way to do this? Thanks!! If I understand what you're asking then you need to expand the range of your lookup table. >'=VLOOKUP(A8,Sheet1!$B$22:$O$22,2,FALSE) You're only u...
MATCH and OFFSET with dynamic range
Hello, I would like to do the following with excel Formula. these are the information Sheet 2 A M 01/12/2001 24 01/06/2002 23 01/12/2002 25 01/06/2003 52 01/12/2003 53 there are already 2 range define in sheet 2 rngDate, rgnValues How to obtain something like this with Excel formula and not VBA Sheet1 Jun Dec tot 2003 52 53 105 2002 23 25 48 2001 24 24 I tried to use Match and Offset (but I have a problem with the offset) Any help will be really apreciate :D Ina I noticed that ...
Can vlookup be used to get a result from an Access Database Table? Thanks. The vlookup function itself is an Excel function and as such can only retrieve data from an excel sheet. However you may be able to use the import external data menu option in the Data menu to retrieve your data from the access database, pull it into your excel workbook and then use the vlookup function. I never tried this myself, but maybe the idea can be applied. -- Jeroen van Nieuwkerk "finster26" wrote: > Can vlookup be used to get a result from an Access Database Table? > > Thanks....
Exporting data using Pulldown List & VLOOKUP
Hi Friends, I am developing a portfolio tracker excel file. I am having some difficulty in implementing one function, which I think I cannot create using macro. I am downloading various parameters using the SMF Add-In. Which is working fine. After downloading these data I want to export them into the other sheet "Pull Down Option". I tried to use OFFSET and VLOOKUP function. But it is not working. In the Pull Down Option sheet, I have created a droop down list, so when I change the droop down list, the data associated (Sheet Data) with it & a stock symbol shou...
VLookup in Excel 2007
In Excel 2003, when you looked up in the Criteria table, the identifier you chose had to be on the end and in alpha numeric order. Is this still the case? If I was to vlook up in multible tables and generate my own unique information, Can I go to a table created in the vlookup and use it even if it is not in alpha numeric order? Is there a work around? I don't really understand your question, but the quick answer is that there is no change in Vlookup from 2003 to 2007. You have always been able to use a table that wasn't in order by using False as the fourth parameter....
VLOOKUP and Ranges
I wonder if you can help me. I am trying to use VLOOKUP to do the following: - I have a two column table, the first has words in it and the second has percentages. - What I want to do is lookup values (from a variety of different cells) and match them to the percentages column - but bring back the words in the first column. The problem is that the values will not be an exact match - as basically the percentages column is a list to define the ranges (i.e. it would say in the first column a quarter and then the second column would say 24%-26%). What I have done is entered "a quart...
Charting with missing data or empty strings from vlookup()
I've used the vlookup() trick -- if(isna(vlookup(...)), "", vlookup(...)) to construct an intermediate table, which I would like to chart. However, charting cells which contain the empty string ("") works porly. I really don't want to treat those missing values as zeros, if there's any way to avoid it -- I just want the chart to treat them as missing. How can I change either the way I'm using vlookup() or the way I'm charting to allow me to chart this data with missing values? Thanks for your help, Mike Ironically, if you'd left the #N/A in t...
Vlookup, COUNTIF, IF or Nested
Hello, I have a work sheet "Raw Data" of about 1500 rows and 30 Columns. In columns K, K, L, M, T and W are names of staff. In worksheet "Lookups" I have a defined list of "Leavers". I need to return the names of the leaver/s in the last column of the "Raw Data" sheet. eg: Row 15 Column K contains "Joe Blog" Column M contains "Micky Mouse" and Column W contains "Jane Doe". in my defined list of leavers "Joe Blog" and "Jane Doe" are listed, in the last column of the "Raw Data&quo...
Offset 2nd ref.
A B ONE 1 TWO 2 THREE 3 ONE 4 Is there a way I can use the offset to look up a duplicate reference? To where I ref ONE and come up with 4. Try the below array formula which will lookup and return the 2nd matching value =INDEX(B1:B10,SMALL(IF(A1:A10="One",ROW(A1:A10)),2)) With text 'One" in cell C1 and the instance number in cell D1 (array entered using Ctrl+Shift+Enter instead of Enter) =INDEX(B1:B10,SMALL(IF(A1:A10=C1,ROW(A1:A10)),D1)) will return the last matching value in B if data is continuous (just works for...
Max Offset
Hi, I've got a range of numbers, J5:J28. I can get the maximum value using: =max(j5:j28) However, I want to find that max number and find the offset value 1 column to the left. Is this possible within a formula? I've tried all sorts of combos with MAX and OFFSET but can't get this one. Any help would be appreciated. Thanks, Dan. -- Voodoodan ------------------------------------------------------------------------ Voodoodan's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=597 View this thread: http://www.excelforum.com/showthread.php?threadid...
vlookup problem #12
Excel 2002 SP3 Win XP HE SP1 *Follow-up group is: microsoft.public.excel* *only reply to: microsoft.public.excel* hi, i am having a problem with vlookup wherein if i use formula A: =VLOOKUP(A2,NJ!$C$2:$C$48,1, FALSE) it works but formula B doesn't: =VLOOKUP(A2,NJ!$A$2:$E$48,5,FALSE) the only difference is the table array range which in the formula B looks into the same sheet etc., but at more columns. i can't understand what is causing the problem. i believe that the columns do not need to be sorted (help file); the sheets are formatted properly, etc. i've done LOTS of vloo...
vlookup help #2
hi i am providing you with my problem with attached file. i will be very grateful to you if you can solve my probem Attachment filename: help me.xls Download attachment: http://www.excelforum.com/attachment.php?postid=46482 -- Message posted from http://www.ExcelForum.com Hi first: you should try to explain your problem in plain text. Most people won't open attachments :-) To your problems: Just enter the 4th parameter of the VLOOKUP function to look for exact matches. So change =VLOOKUP(A17,A25:C27,3) to =VLOOKUP(A17,A25:C27,3,0) and =VLOOKUP(A17,A25:C2...
Exponential trendline offset
I have set the normal exponential trendline to a scatterchart but have to add an offset to the equation (instead of y = a * e^bx, I would like to use y=(a*e^bx) + c. How can I do that in Excel? Thanks in advance. Juliano Fernandes -- drjulianof ------------------------------------------------------------------------ drjulianof's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=30685 View this thread: http://www.excelforum.com/showthread.php?threadid=503477 If I want to fit to y=(a*e^bx) + c, I would use Solver If interested sent message to my private email w...
IF & Vlookup Assistance
Evening Chuck I tried your suggestion =IF(ISNA(VLOOKUP(A9,'Schedule'!$B$2:$C$20882,2,FALSE)),"Invalid Number"&","&VLOOKUP(LEFT(A9,4),$A$2:$C$20882,2,FALSE),VLOOKUP(A9,'Schedule'!$B$2:$C$20882,2,FALSE)) I got a messg #N/A. I tried it in a smaller size worksheet. example Sheet 1 is where I am doing the Vlookup from. Sheet 1 has 3 column with data col A contain both 8 digits and 4 digit user Id Col B contains the location and Col C contains the terminal ID. Sheet 2 is where I have the formulas doing the IF & Vlookup from. Col A is where I ...
VLOOKUP/HLOOKUP Question
Hi, I am trying to get Excel to look up a certain value from a small tabl of values and have tried using the VLOOKUP/HLOOKUP functions but can' get them to do exactly what I want. Here is how the spreadsheet looks: Recommended Tank Size: 4400 litres Range of Tank Sizes Available (litres): 1000, 2000, 3000, 4000, 5000, 6000, 7000, 8000, 9000, 10000 Basically their is an algorithm which calculates the "Recommended Tan Size" and then Excel is supposed to look up the nearest but nex -largest- value from the "Range of Tank Sizes Available" table. The problem I am havin...
Vlookup and multiple data
this is in my 1st work sheet (a1) 500 (b1)Bob (c1)Yes (d1)hants (A2) 500 (b2)Dave (c2)No (d2)surrey (A3) 500 (b3)Rob (c3)Yes (d3)york this is the formula i am using in the next worksheet for a drop down list =IF(VLOOKUP(C8,PowerGate!A3:$L$100,3,FALSE)=0,"",VLOOKUP(C8,PowerGate!A3:$L$100,3,FALSE)) It will show the 500 seperately but what i would like to do is when you select the 500 is to have all of the information for 500 shown in the next sheet.I don't want to have to select it individually. Hope this makes sense. Do you want the data for all rows wit...
Getting the next higer value with Vlookup
In VLookup / Hlookup function the True parameter gives the closest match with the search value but less than the search value.... i want to get the next greatest values of the Search Value.... how to do it.... Example ..... A B C 5 10 15 6 11 16 9 14 19 If i use =Vlookup(7,[Array],2,0)/ =Vlookup(7,Array],2,True) .... the answer is get is 11 BUT how to get it as 14!!!??? Please reply me at : getu32@gmail.com Hi One way =INDEX(B2:B4,MATCH(7,A2:A4,1)+1) -- Regards Roger Govier "getu32" <getu32@gmail.com> wrote in me...
How to use vlookup in an array formula
In sheet1, I have rows and columns whose cells contain strings like "S", "M", "L", and "XL". Some cells are empty. In sheet2, is a column A of the same strings and a column B of corresponding values, e.g. 1 corresponds to "S", 4 corresponds to "M", etc. I want to be able to sum the rows and columns in sheet1 based on the table in sheet2. The array in sheet1 is not the same size as the value column in sheet2. I tried to use an array formula like {sum(vlookup(sheet1!a1:a20,sheet2!a1:b4,FALSE))} but it doesn't seem to...
Offset, Dynamic range, Countif
I need to count the # specific items within a list of data (in Column K). Column K data is limited to four "Status" responses ("Confirmed" "Good" "Bad" "Non-Responsive"). The corresponding dates (in Column J) tells me the date these items were entered into system. I would like to have a formula which looks at the date within cell A3 (a variable input cell allowing me to search using a specific date) and looks only within the date range under column J and counts the # of "Non-Responsive" cells under column K (still abiding by th...
vlookup?? #2
I'm not sure how to do this but this is what I want to do. A B 6/27/05 262A029 6/23/05 267A042 6/24/05 267A043 6/24/05 271A001 I want to search column A for a certain date and pull out the data on column B but some dates have multiple matches on column B. I need all the column B data that matches the date. So in the example I have...When I search for 6/24/05 in column A I get "267A043" and "271A001" from column B. Can someone please help me!!! Thanks -- cutsygurl -----------------------------------------------------------------...
Dynamic Chart (OFFSET Function) plots empty cells.
I have read the posts about Excel charts plotting cells that are empty. I'm not sure I understood what I read, and our particular circumstances may be a bit different. (I'm not sure.) Anyway. Our problem is: We use Excel 2003 MSQuery to extract the results of an Access query and automatically put them in an Excel spreadsheet. Depending on the circumstances, the query doesn't always return the same amount of data. In order to automatically expand or shrink the range of cells that actually contain data, we use the OFFSET function DonationsTotals=OFFSET(Donations!$K$2,0,0,COUNTA(...
Preventing hyperlinks and vlookups from breaking
We are planning a re-organize our primary Drive and will be moving most of our folders and subfolders. We commonly use hyperlinks between workbooks. We are an Excel dependent department and have hundreds of files within our primary folders and subfolders. During testing, I find that when the source workbook is moved, the hyperlink in the dependent workbook no longer works and I get: "Cannot open specified file." I need to move the folders and subfolders soon and need to know how to prevent this error from occurring when the users try to use the hyperlinks. Als...
convar and offset queston
Hi all I try to do convar(A1:$A$10,$B$1:B10) convar(A2:$A$10,$B$1:B9) convar(A3:$A$10,$B$1:B8) convar(A4:$A$10,$B$1:B7) ..... convar(A10:$A$10,$B$1:B1) Getting the first argument (A1:A10) -> (A10:A10) with problems but how to get (B1:B10) -> (B1:B1) I tried to use Offset to decrement the ending cell without any success. Some knows how to do it? Cheers Wing GMB wrote: > Hi all > > I try to do > > convar(A1:$A$10,$B$1:B10) > convar(A2:$A$10,$B$1:B9) > convar(A3:$A$10,$B$1:B8) > convar(A4:$A$10,$B$1:B7) > .... > convar(A10:$A$10,$B$1:B1) > >...
Limit to nested Vlookups
Is there a limit to the number of nested Vlookups you can have -- karambo ----------------------------------------------------------------------- karambos's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1626 View this thread: http://www.excelforum.com/showthread.php?threadid=39940 There's a limit of 7 nested levels (not just for =vlookup()). karambos wrote: > > Is there a limit to the number of nested Vlookups you can have? > > -- > karambos > ------------------------------------------------------------------------ > karambos's ...
What cell is vlookup returning
Can anyone tell me what cell a vlookup is returning? Example if the vlookup function returns the value 100, what cell did that value of 100 come from. I have tried CELL("address",VLOOKUP(E5,G:J,4,FALSE)) but it does not work? Sorry for so many questions today. Thanks! ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ Try =CELL("address",INDEX(J1:J1000,MATCH(E5,G1:G1000,0))) -- Regards, Peo Sjoblom "jpx" <jpx.uxu8z@excelforum-n...
Vlookup & relative cell reference?
Hello-- I have a formula entered as follows: '=VLOOKUP(A8,Sheet1!$B$22:$O$22,2,FALSE) My Sheet 1 - match column is this: Row Column B 22 Store 1 - Primary 23 Store 1 - Secondary I'd like to automatically have Vlookup return the values from Sheet 1, in Row 23 without having to type that label in my original sheet, cell A9. Is there any quick way to do this? Thanks!! If I understand what you're asking then you need to expand the range of your lookup table. >'=VLOOKUP(A8,Sheet1!$B$22:$O$22,2,FALSE) You're only u...
MATCH and OFFSET with dynamic range
Hello, I would like to do the following with excel Formula. these are the information Sheet 2 A M 01/12/2001 24 01/06/2002 23 01/12/2002 25 01/06/2003 52 01/12/2003 53 there are already 2 range define in sheet 2 rngDate, rgnValues How to obtain something like this with Excel formula and not VBA Sheet1 Jun Dec tot 2003 52 53 105 2002 23 25 48 2001 24 24 I tried to use Match and Offset (but I have a problem with the offset) Any help will be really apreciate :D Ina I noticed that ...