Formula to input value
 

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

Formula to input value
 

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" wrote:

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

Formula to input value
 

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" wrote:

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" wrote:

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

Formula to input value
 

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


"Gemi" wrote:

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" wrote:

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" wrote:

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

Formula to input value
 

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

On Wed, 1 Apr 2009 11:50:14 -0700, JonR
wrote:

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)