Please help me with function

[Brian]

when i enter an employee ID number in cell A1 only, I want
cell B1 to automatically display the employee's name.

for example,
A B
1 356 1 John Doe

I will be changing the ID number often and will always be
working in the same cells. In cell B1 I used the formula:
=if(A1=356,"John Doe") this works but only for entering
one ID number. Essentially I am looking for the formula to
simply state If A1 = 356 then enter John Doe in B1 but if
A1 = 456 then enter Robert Smith, and so on for several
other employees.

Thank you,
Brian

 

[Kevin Stecyk]

Brian,

You are looking for the VLookup function.

Have a table where you have the IDs and Names listed. Say Col J is IDs and
K is Names.

Then in B1, you could write,

=Vlookup(A1, J1:K50, 2, false)

The last argument where I have "false" is helpful if the IDs are not in
ascending order.

Hope that helps.

Regards,
Kevin

 

[Felipe]

Brian,

You can use a VLOOKUP.

In another part of the sheet, or another sheet or
workbook, build a table where you list all employee ID
numbers and names.
In the following example this range is in Book2, sheet1,
A1:B100.

In B1 in your original sheet write:
=VLOOKUP(A1,[Book2]Sheet1!$A$1:$B$100,2,0)

Drag down the formula.

Regards,
Felipe

 

[Guest]

Thank you
I will try that now, I'm not that great with this stuff so
I hope I can do it.
Thanks again.

 

[Gord Dibben]

Brian

An example only........

In column A enter the ID numbers(A1:A20)

In column B enter the names(B1:B20)

In D1 enter =VLOOKUP(C1,$A$1:$B$20,2,false)

Type a number into C1 and a name will appear in D1.

A further refinement would be to use Data Validation in C1(in this example)
with your ID numbers as the "list" to choose from.

For more on Data Validation see Debra Dalgleish's site.....

http://www.contextures.on.ca/xlDataVal01.html

Gord Dibben XL2002

 

[Jeane]

There are a couple steps needed to make this work well.
Create a sheet that has the employee number in column A,
name in column B. A1 and B1 should have your headers,
Next sort the data on this sheet on column A, ascending.

On Sheet 1, create a column that can be hidden. Let's use
C for this example. Assume the employee number is typed
into cell A1 and the name is to be in B1. In C1, type
MATCH(A1,Sheet2!$A$2:$A$4,0) Where the Sheet2 range is
just the range of employee numbers. That will return the
row the number can be found on.

Next, in the cell where you want the name (B1), type =IF
(ISNA(C1),"",VLOOKUP(A1,Sheet2!$A$2:$B$4,2,FALSE)). How
this statement works is. If C1 is false, B1 is blank,
otherwise Lookup the value in A1 in the range A2:B4 and
return the value in the 2nd column.

The VLookup function will always return a value. If the
number doesn't exist, it will return the next valid number
which, of course, may be the wrong info. The Match
function will make sure the number you typed exists. If
it doesn't, the cell (B1) will remain blank rather than
show incorrect data.

 

[Kevin Stecyk]

Jeane,

<<The VLookup function will always return a value. If the
number doesn't exist, it will return the next valid number
which, of course, may be the wrong info.>>

I don't believe that is true when the last argument is set to false. The
VLookup must find an exact match. If it can't, it returns, #na. And it
also eliminates the need to sort the data in ascending order.

Regards,
Kevin

 

[pfsardella]

You should take a look at the LOOKUP functions in help.

Create a table with the data; then, use a formula to look up that
value.

David McRitchie offers some information abut how to use this function.

http://www.mvps.org/dmcritchie/excel/vlookup.htm

HTH
Paul