Need formula or VBA code
 

Hello -

I need a formula or code for the following scenario:

Sheet 2 has two columns; one with the name (A) and one with the employee
number (B).

Sheet 1 has many columns, but the ones I'm concerned with are Employee Name
(Column A - value I need to look up and insert) and the employee number in
Column B.

I would like to look up the employee number on Sheet 2, column B, then take
the corresponding employee name from Sheet2 Column A and put it into Sheet1,
Column A.

Any help will be greatly appreciated!

--
Sandy

Need formula or VBA code
 

this looks like a simple lookup formula

here is a great example that shows VLOOKUP

http://www.datapigtechnologies.com/f...s/vlookup.html


--
davesexcel
------------------------------------------------------------------------
davesexcel's Profile: http://www.excelforum.com/member.php...o&userid=31708
View this thread: http://www.excelforum.com/showthread...hreadid=531194

Need formula or VBA code
 

Hi

The easiest solution is to redesign the sheet 2 so, that employee nimbers
are in column A, and employee names in column B (simply move names column to
right of numbers column, and delete empty column from left). This allows you
to use VLOOKUP to get employee name from its number - otherwise you have to
use more complex formulas.

Now define a dynamic named range EmployeeTable on Sheet 2 (I assume the 1st
row contains column headers)
=OFFSET('Sheet 2'!$A$1,1,,COUNTA('Sheet 2'!$A:$A)-1,2)
(This named range refers always to whole table, when you add or delete
employees - lest you don't have empty rows there. You can skp this part, and
use common range reference in following formula instead, when you have any
difficultis with it.)

On sheet 1 to you use VLOOKUP formula like this
=VLOOKUP($A2,EmployeeTable,2,0)



Arvi Laanemets



"Sandy" wrote in message
...
Hello -

I need a formula or code for the following scenario:

Sheet 2 has two columns; one with the name (A) and one with the employee
number (B).

Sheet 1 has many columns, but the ones I'm concerned with are Employee
Name
(Column A - value I need to look up and insert) and the employee number in
Column B.

I would like to look up the employee number on Sheet 2, column B, then
take
the corresponding employee name from Sheet2 Column A and put it into
Sheet1,
Column A.

Any help will be greatly appreciated!

--
Sandy

Need formula or VBA code
 

With the sample data and =vlookup() that was used in that presentation, I'm kind
of surprised that the formulat wasn't:

=vlookup(a2,key,2,false)
(added the 4th parm)

And maybe a note about changing the format doesn't mean that the underlying
value will change.

To the OP:

You may want to read Debra Dalgleish's notes:
http://www.contextures.com/xlFunctions02.html (for =vlookup())
and
http://www.contextures.com/xlFunctions03.html (for =index(match()))



davesexcel wrote:

this looks like a simple lookup formula

here is a great example that shows VLOOKUP

http://www.datapigtechnologies.com/f...s/vlookup.html

--
davesexcel
------------------------------------------------------------------------
davesexcel's Profile: http://www.excelforum.com/member.php...o&userid=31708
View this thread: http://www.excelforum.com/showthread...hreadid=531194

--

Dave Peterson

Need formula or VBA code
 

Vlookup won't work in this instance. Instead, you need Index and match.

In A1 of sheet1 put in

=Index(Sheet2!A:A,Match(B1,Sheet2!B:B,0),1)

then drag fill down the column.

If the ID's start in row2 of sheet1


In A2:
=Index(Sheet2!A:A,Match(B2,Sheet2!B:B,0),1)

--
Regards,
Tom Ogilvy



"davesexcel" wrote
in message ...

this looks like a simple lookup formula

here is a great example that shows VLOOKUP

http://www.datapigtechnologies.com/f...s/vlookup.html


--
davesexcel
------------------------------------------------------------------------
davesexcel's Profile:
http://www.excelforum.com/member.php...o&userid=31708
View this thread: http://www.excelforum.com/showthread...hreadid=531194

Need formula or VBA code
 

Thanks All!!
--
Sandy


"Sandy" wrote:

Hello -

I need a formula or code for the following scenario:

Sheet 2 has two columns; one with the name (A) and one with the employee
number (B).

Sheet 1 has many columns, but the ones I'm concerned with are Employee Name
(Column A - value I need to look up and insert) and the employee number in
Column B.

I would like to look up the employee number on Sheet 2, column B, then take
the corresponding employee name from Sheet2 Column A and put it into Sheet1,
Column A.

Any help will be greatly appreciated!

--
Sandy