=Lookup function not working, are there alternatives?

Hi,

I'm trying to do a lookup. User will enter an account number which can be all numerica or alpha numeric. I then need to do a lookup to a large table based on their entry. When the lookup finds a match in A:A of the table, I need it to return the value in column N.

The table can contain multiple instances of the account number, but the value in column N will always be the same.......so all I really need Excel to do is fine the first instance of the account number, and return the value in column N of that field.

I've tried using =LOOKUP(C1,'Sheet1'!A:A,'Sheet1'!N:N) The result is inconsistant and not always correct. I've tried sorting my table by Column A, I've tried formatting the field as number, text, general....it remains inconsistant.

Is there another formula to find and return the information in Column N that I need, or am I just doing something wrong? I've used Lookups quite a bit, but Excel sometimes gets tempermental with them.

Thanks,
Ben

Thanks...I've uploaded a sample Excel document. In this, in the "Est to Actual Compare' tab, user will enter an account number in Cell C1. Cell I1 contains the lookup formula, which reads from tab CMPM Data.

What I want this to do is whatever value is in C1, then lookup in A:A of the CMPM Data and find a match. When a match is found, then return whatever value is in column N.

In the uploaded sample Excel I have entered account 221051. It's a valid account in column A:A of CMPM Data, and it should return an "N" from Column N, however I'm getting #N/A.

If I try entering 126812 in cell C1....it returns the value in column N correctly.

I can't figure out why it works some of the time, but not all. Is there another way to search column A:A based on entry in Cell C1, and have Excel return the associated value in Column N?

Thanks, appreciate your help!
- Ben

Yes, I know #N/A means value is not found, but that is not correct for 221051. If you look in the table, value 221051 begins in Cell A165. Even in the vLookup formula you provided, it should capture 221051 correctly and return a 'N' from column N....but I'm still getting #N/A.

I believe this has something to do with formatting. If I copy 221051 from cell A165 and paste it into cell C1 where users will enter the account, I then get the correct outcome and it returns 'N'. If it is a formatting issue, my problem is I can set the format a certain way, but users may manually enter in C1 or they might copy/paste the number from another Excel document. I can't necessarily control the formatting because of this.

Because of this, and if the Lookup functions require like formatting, I'm just wondering if there is another formula that could be used to find the Account number and then pull in the value of Column N in that same row.

icestationzbra...thanks so much!!! I tried the =Index formula and it seems to work correctly in all cases. Not only does it pick up correctly if Numeric only or Alpha-Numeric, but I can add additional spaces and it still works. This is great...you saved me a lot of headache!!!

- Ben