If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
using vlookup
while I have been a user of Excel for awhile, I have never really used to any
of it potianl. Well now is the time to start using some more features of Excel. What I am looking to do is place a value in one cell based on the value of another cell. I have looked at vlookup, that is how i have my data placed, I can understand how that works when you use the vlookup formula and puts the data it needs into that cell. My question is how do I use the static table of data to place one value from a cell into a different cell. Example. Cell A1 has a text value, so based on that text value I would like to use a lookup table or if there is something better that is ok also, to place a value in cell A2 and if I change cell A1 then the value in cell A2 changes. Thanks Jack |
#2
|
|||
|
|||
using vlookup
Suppose your lookup table looks like this:
Jack 4 Sales Pete 3 Admin Dave 5 Finance and it occupies cells K1:M3. If you have a name in A1, you can put this formula in B1: =VLOOKUP(A1,K1:M3,2,0) and this one in C1: =VLOOKUP(A1,K1:M3,3,0) so that if you put Pete in A1 it will return: 3 Admin and if you then type Dave into A1 the values in B1 and C1 will immediately change to: 5 Finance The formulae will try to find an exact match between A1 and the leftmost column of the table, and if there is a match the first formula will return data from the seecond column of the table on the corresponding row, and the second formula will return data from the 3rd column of the table. Does this help to make it clearer? Pete On Jul 11, 12:50*am, Jack wrote: while I have been a user of Excel for awhile, I have never really used to any of it potianl. Well now is the time to start using some more features of Excel. What I am looking to do is place a value in one cell based on the value of another cell. I have looked at vlookup, that is how i have my data placed, I can understand how that works when you use the vlookup formula and puts the data it needs into that cell. My question is how do I use the static table of data to place one value from a cell into a different cell. Example. Cell A1 has a text value, so based on that text value I would like to use a lookup table or if there is something better that is ok also, to place a value in cell A2 and if I change cell A1 then the value in cell A2 changes. Thanks Jack |
#3
|
|||
|
|||
using vlookup
I've used B1 and C1 to illustrate the points, but you could put the
formulae in A2 and A3 if you prefer that layout. Pete On Jul 11, 1:12*am, Pete_UK wrote: Suppose your lookup table looks like this: Jack * * 4 * * Sales Pete * * 3 * * Admin Dave * * 5 * * Finance and it occupies cells K1:M3. If you have a name in A1, you can put this formula in B1: =VLOOKUP(A1,K1:M3,2,0) and this one in C1: =VLOOKUP(A1,K1:M3,3,0) so that if you put Pete in A1 it will return: 3 * *Admin and if you then type Dave into A1 the values in B1 and C1 will immediately change to: 5 * *Finance The formulae will try to find an exact match between A1 and the leftmost column of the table, and if there is a match the first formula will return data from the seecond column of the table on the corresponding row, and the second formula will return data from the 3rd column of the table. Does this help to make it clearer? Pete On Jul 11, 12:50*am, Jack wrote: while I have been a user of Excel for awhile, I have never really used to any of it potianl. Well now is the time to start using some more features of Excel. What I am looking to do is place a value in one cell based on the value of another cell. I have looked at vlookup, that is how i have my data placed, I can understand how that works when you use the vlookup formula and puts the data it needs into that cell. My question is how do I use the static table of data to place one value from a cell into a different cell. Example. Cell A1 has a text value, so based on that text value I would like to use a lookup table or if there is something better that is ok also, to place a value in cell A2 and if I change cell A1 then the value in cell A2 changes. Thanks Jack- Hide quoted text - - Show quoted text - |
#4
|
|||
|
|||
using vlookup
VLOOKUP function could be what you need.
You need a lookup range of at least two columns and a cell for "what" to lookup. Visit Debra Dalgleish's site for info on VLOOKUP function to pull data based on value. Note the downloadable sample workbooks. http://www.contextures.on.ca/xlFunctions02.html Also see Debra's site for info on Data Validation selection lists for inputting the data if need be. http://www.contextures.on.ca/xlDataVal01.html Gord Dibben MS Excel MVP On Thu, 10 Jul 2008 16:50:00 -0700, Jack wrote: while I have been a user of Excel for awhile, I have never really used to any of it potianl. Well now is the time to start using some more features of Excel. What I am looking to do is place a value in one cell based on the value of another cell. I have looked at vlookup, that is how i have my data placed, I can understand how that works when you use the vlookup formula and puts the data it needs into that cell. My question is how do I use the static table of data to place one value from a cell into a different cell. Example. Cell A1 has a text value, so based on that text value I would like to use a lookup table or if there is something better that is ok also, to place a value in cell A2 and if I change cell A1 then the value in cell A2 changes. Thanks Jack |
#5
|
|||
|
|||
using vlookup
Also checkout http://cpearson.com/excel/mainpage.aspx "Jack" wrote in message ... while I have been a user of Excel for awhile, I have never really used to any of it potianl. Well now is the time to start using some more features of Excel. What I am looking to do is place a value in one cell based on the value of another cell. I have looked at vlookup, that is how i have my data placed, I can understand how that works when you use the vlookup formula and puts the data it needs into that cell. My question is how do I use the static table of data to place one value from a cell into a different cell. Example. Cell A1 has a text value, so based on that text value I would like to use a lookup table or if there is something better that is ok also, to place a value in cell A2 and if I change cell A1 then the value in cell A2 changes. Thanks Jack |
Thread Tools | |
Display Modes | |
|
|