Need a formula to display value from lookup table to worksheet

Posted by Dawn on July 24, 2001 7:20 AM

Hi,

I have multiple financial worksheets that display a value (employee) but I need to determine who the mgr is. So, I have a lookup table of sorts that displays the employee in a column, the next column displays the manager.

How do I put together a formula in the worksheet to say look at the column that shows the employee (current worksheet), go look at the lookup table, determine the mgr and deposit that into the current worksheet.

Another issue, is that the employee (current worksheet) has a number after the name but it doesn't correspond with the lookup table.

Please help me...

Thanks,
Dawn

Posted by IML on July 24, 2001 7:42 AM

You should be able to use the Vlookup function to achieve this. However, this requires and exact match of name to name, so we will need to manipulate your data a little. How exactly is the name / number listed?
Is it
SMITH 123
SMITH123
SMITH(123)
etc.

Posted by Dawn on July 24, 2001 8:23 AM

Hi,

The name/number is listed?
jansmith01003 or
jsmith01003

The lookup section has the following
Number
email id
Name
Mgr

So, I'm trying to have the formula look at the lookup, check the name/number, then deposit the mgr in the mgr field of the original worksheet.

Does this explain enough?

~Dawn

Posted by IML on July 24, 2001 8:36 AM

Lets give this a try. Highlight your lookup section and name it via the name box. I used "mngr".
In the cell you want to return the mananger name, use the formula

=VLOOKUP(RIGHT(A1,5),mngr,4,FALSE) where your name number combination is in cell A1.

I made a couple of assumptions here, which may be good or bad.
1) The number is a better key, since the name can vary (jsmith vs jansmith)
2) All the numbers are treated as text. (your look up table has '01003 for smith, not 1003
3) All numbers are 5 digits long.

If any of these aren't true, we'll need to play a little more with it. The real key is consistency in what you are looking up with your list.

good luck

Posted by Dawn on July 24, 2001 10:08 AM

Hi,

Thanks for the suggestion.

I actually had a co-worker help me and this is what we came up with....

=LOOKUP(LEFT(A9, FIND("0",A9)-1),'LOOKUP'!B1:B91, 'LOOKUP'!D1:D91

A9 - Is where the name/number (# always starts with 0)
'LOOKUP' - Is where the data is
B - is the name
D - is the mgr

Thanks again for your help. I think I will try your way as well.

~Dawn

Posted by IML on July 24, 2001 10:30 AM

No problem. If your name is distinct and will be the list this should work fine. We just may need to tweak the rest of the formula slightly.

The first argument looks good. the leftA9,find(... will come up with your name ie jansmith.
The next argument is where you want to look up from. If the names are in col B and the managers are in col D, your range should be B1..D91.
The third agrument tells what column from your range you want to return. In this case it would be 3. (B is 1, C is 2, D is 3)
The last argument False, tells it to return an exact match.

So putting it all together would be something along the lines of
=VLOOKUP(LEFT(A9,FIND("0",A9)-1),LOOKUP!B1:D91,3,FALSE)