using vlookup

  • Thread starter Thread starter Jack
  • Start date Start date
J

Jack

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
 
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
 
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
 
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
 
Back
Top