lookup in Excel 2000

[stubnski]

Hi everyone,
Here is my code -

=PROPER(LOOKUP(A2,'C:\[Member Data.xls]MBRDATA'!$A$2:$A$600,'C:\[Member Data.xls]MBRDATA'!$D$2:$D$600))

Column A are numbers(account #'s)
Column D is text formated as General(Names).

When I paste the code down the column all the records return what I need, all except for the very last record which returns a zero. Both workbooks are formated exactly the same(cell formats, headers...) Why would the last row return a zero when all the other rows return the desired information? I hope I explained well enough. TIA


Stubnski

 

[SkipVought]

is your data sorted ascending?

Skip,

_{[red]Be Advised![/red] Dyslexic poets...
write inverse!}

 

[stubnski]

Thanks for the reply. Yes it is in ascending

 

[SkipVought]

Please COPY 'n' PASTE the last row VALUES.

Skip,

_{[red]Be Advised![/red] Dyslexic poets...
write inverse!}

 

[stubnski]

This is the data from the source workbook -
Very last record
account 1, account 2, Fname, Lname, B-date....
192131 2131.0 JANE SMITH 19-Feb-51

This is the result workbook -(all I get is Account 1 and name, address(city, state, zip all seperate columns)

account Fname Lname Street, City, State, Zip
161192 0

The account is entered manually, all text is blank, and the zip comes back as zero. I have made sure that the record is in the source workbook.

 

[UpstateNYAdmin]

This maybe a dumb question but, does that acct # come back when it is NOT the last in the list?

 

[AnotherHiggins]

Try to copy the account number from the 'source workbook', then paste it into the 'result workbook', just to remove any possibility that there is an extra space or something like that throwing things off.

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

Help us help you. Please read FAQ181-2886 before posting.

 

[stubnski]

UpstateNYAdmin - No, the result does not change when it's not the last record, still blank. The last record was fine so it must just be that one record. I've deleted the row with the bad record to see if the next record up(now last record) would do the same, but it didn't. I've reentered the record's information in the source workbook - still same result.

anotherhiggins - Did that and same result. I hard coded the record (='C:\[Member Data.xls]MBRDATA'!$D$583) just to get the report out, but I was still curious as to why the lookup didn't work for that particular record.

Thanks again for all the help!

Stubnski

 

[SkipVought]

Your examples are NOT consistent.

I asked for ALL THE DATA in the last row.

So you post...
[tt]
account 1, account 2, Fname, Lname, B-date....
192131 2131.0 JANE SMITH 19-Feb-51
[/tt]
and
[tt]
account Fname Lname Street, City, State, Zip
161192 0
[/tt]
what's going on???

Shoot from the hip possibility...

Your ZIP column contains BOTH NUMERIC (5-digit zips) and STRING (5+4 which are STRINGS) -- VERY CONFUSING to Excel.

If this is the case make ALL ZIPS STRINGS. Numbers and numeric strings dont't mix well as they are not similar beasts!

Skip,

_{[red]Be Advised![/red] Dyslexic poets...
write inverse!}

 

[stubnski]

Skip thanks for the reply, and sorry for the confusion.

The reason I didn't put all of the columns in the first time is because there are alot of columns in the source workbook. It contains all of the info from a very, very small credit union(we're merging with them) and their systems do not like our systems. Until we're merged, the members info was put into Excel. But anyways the zip is all numeric with no dashes. Everything else I need - names, address's... are all formated as general.

The second set of data with the zip in it is what the record returns with the formula.

Sorry if I confused you even more, but it's just one of those days for me.
Thanks again

Stubnski

 

[SkipVought]

You persist in failing to post the data that is essential to see!

You could, quite easily, HIDE the columns that are of no consequence.

Clear, consistent and concise!

Cannot help you otherwise!

Skip,

_{[red]Be Advised![/red] Dyslexic poets...
write inverse!}

 

[stubnski]

Skip here is source workbook info. Everthing is formated as general except for the first and last(163054, 98368) which is numeric.
Here's the layout -
acct # fname lname street city state zip

163054 JEFF STANTON 816 DISCOVERY LANE PORT TOWNSEND WA 98368


Once again sorry for the stupidness.

Stubnski


Humans are good students. God created misery, we created computers.

 

[SkipVought]

If you were to delete all columns EXCEPT the acct # and zip, send it (all 600 rows) to

skipandmary1017 at mindspring dot com

I'll take a look.

Skip,

_{[red]Be Advised![/red] Dyslexic poets...
write inverse!}