EXCEL: lookup using 2 spreadsheets

[sjdk]

Hello! Thanks in advance for any assistance...
I am working on moving data from one database to another. Right now I have my data in 2 excel spreadsheets. One is EvalsFinal.xls, the other is ElmPosition.xls. In EvalsFinal, I have (among other things) an employee id(text(6)) in ascending order. In ElmPosition I have (among other things) Employee ID (text(6)) ascending order and PosClass(text(2)). In evalsFinal, I need to have the value that is in ElmPosition PosClass appear in a blank column. In EvalsFinal, i tried putting =LOOKUP(A2,[ElmPosition.xls]Sheet1!$A$2:$A$3824,[ElmPosition.xls]Sheet1!$D$2:$D$3824) in cell L2 and the result is #N/A. Sample data...
EvalsFinal...Employee Id....
A2...00007
A3...00009
A4...00012

ElmPosition...Employee Id (colA)...PosClass (colD)
A2...0001
A3...0002
A4...0003
A5...0004
A6...0005
A7...0006
A8...0007...D8...CL

A8 is my first match to EvalsFinal(A2) and i would like the value CL to appear in EvalsFinal(L2).

Where did I go wrong? I hope I provided enough info to give a clear picture of what I am trying to do.

This is a one-time thing...once I get my PosClass in the EvalsFinal spreadsheet, I can load the data into the new database and be done with this forever.

Again....thanks for any assistance!

 

[AnotherHiggins]

Try this:

[tab]=VLookUp(A2,[ElmPosition.xls]Sheet1!$A$2:$D$3824, 4, 0)

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[sjdk]

Thanks for the response!
I put that in and received the same result, #N/A in cell L2.

 

[SkipVought]

Hi,

Check out HELP. #N/A is Excel's way of giving you a hint about what's wrong.

Your lookup value has NOT been found in the lookup range!


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[AnotherHiggins]

Getting #N/A for a VLookup indicates that the value you're looking for wasn't found in the target range.

The value's you're looking for must be in the first column of the lookup range - that is, "00007" must be in Column A of [ElmPosition.xls]] for the formula I posted to work.

Other than that, my first thought is that there is some difference in the data on one sheet and the other.

Try this as a test (using your sample data from the first post):

- In [EvalsFinal]A2 (beside 00007), type in =A2=
- Then brose to the other workbook and click on the corresponding value in [ElmPosition]A8

This will return either TRUE or FALSE. The idea is that we're asking Excel if the two values that LOOK the same really are identical.

If you get false, click into each of those two cells and look to see if there are any leading/trailing spaces before/after the "00007".

Please let us know if you get TRUE or FALSE in the above test.

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[sjdk]

UUUUGGGGGHHHH! I am such a dork! That true/false test is cool! Naturally, it came up False....I neglected to convert my numbers to text before I started this little excercise! I got it (using my original lookup).
Many many thanks for your time and assistance!!

 

[AnotherHiggins]

Glad we could help



[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[andrewjj]

Hello All,
Question: Do these two Excel sheets need to be in the same workbook? If not could the sheets reside in different logical drives.
Thanks

 

[Gavona]

vlookup([red]--[/red]A1,......
Will convert A1 to a number if it is easier than converting in some other way.

Whilst the files can be on different logical drives if you are doing multiple lookups you will find it beneficial to have both files open. (In some cases (hundreds of lookups) with out opening file the workbook may freeze or at least take hours to calculate). Open using Edit, Links, OpenSource


Gavin