Formula to input value

[Gemi]

Hello,
I ahve a worksheet that returns employee data, the employee is not listed my
name but by a letter and number. I want to run a formula that would input the
employees name in Column A that is associated with the appropriate employee
number in Column B. Would I create a list of all the employee names along
with the associated number and run a macro?

i.e.
Column A Column B
Jones H12
Jones H12
Smith H42
Johnson H10
Johnson H10
Johnson H10
Kelly H16
Kelly H16
Thomas H18
Thomas H18


Any help is greatly appreciated!

Thanks,
Lisa

 

[JonR]

You can use a VLOOKUP function

build a data table (column A must be sorted in ascending order)

A B
H10 Johnson
H12 Jones
H42 Smith

Lets say you have the employee number in cell C1

The formula is =VLOOKUP(C1,A1:B3,2)

This ltakes the value in C1, looks it up in column A and returns the
relevant value from column B. You can put your lookup table on a separate
sheet by specifying the sheet name in the formula
=VLOOKUP(C1,'SheetName'!A1:B3,2)
--
HTH

JonR

Please rate your posts

 

[Gemi]

Jon,
Thank you for your help. Here is were I'm at:
I created the data table, a total of 17 employees the table array is A2:B18.
I entered the formual in Column C - Employee number begins at D1 and ends at
D2377 (the amount of rows will vary with each worksheet). When I go to carry
the formula down the entire column is stops at row 18 where the data table
ends and it is only returning the employee name in C2 all others are #N/A.
The table array is changing in each cell. It starts correctly A2:B18 then
goes as such:
A3:B19, A4:B20, A5:B21 etc.
I would need the table array to remain the same for all the rows and just
the lookup value change.
Any suggestions?

Thank you,
Lisa

 

[JonR]

Lisa,

My bad -- I should have told you to use absolute references for your data
range

=VLOOKUP(C1,$A$1:$B$3,2)

The $ character 'locks' the reference to the column or row. This way when
you drag the formula down you will continue to refer to the correct range of
cells.

=VLOOKUP(C1,$A$1:$B$3,2)
=VLOOKUP(D1,$A$1:$B$3,2)
=VLOOKUP(E1,$A$1:$B$3,2)
--
HTH

JonR

Please rate your posts

 

[Gord Dibben]

And you don't need the list sorted if you add the FALSE argument.

=VLOOKUP(C1,$A$1:$B$3,2,FALSE)

Which will return #N/A is no match found.

You may want to mask the error by adding the ISNA function.

=IF(ISNA(VLOOKUP(C1,$A$1:$B$3,2,FALSE)),"",VLOOKUP(C1,$A$1:$B$3,2,FALSE))


Gord Dibben MS Excel MVP