Lookup functions always return n/a ???
 

I have a large work book that that contains approx 2200 rows of information. Each row contains about 100 columns of information and each of these pieces of information come from look-up functions from a different sheet. My objective with this workbook was to create it in a way that I could use it as a template to create other sheets of information with similar properties. The purpose of this project was to take knowledge that wasn't available to other employees in my company and make it available to them without the time it takes to research the meanings of the different codes in the workbook.

That all being said, sorry I tend to be long winded, I prefer to use index/match formulas as opposed to other look-up functions because it is so versatile. My problem comes in that I keep getting a n/a return on a specific formula located on a sheet in the book labeled 'Look-up Sheet'. This formula is supposed to take the user's input and look back at a sheet called 'H-DATA' and return the corresponding information.... it doesn't work and I can't figure out why. I have tried lookup, vlookup, & index/match. I have tried slapping my computer and calling it ridiculous name even with no result other than n/a.

I am currently using Excel 2003, because my company refuses to update. Have I reached the limits of this Excel version? I would be happy to post this workbook but I'm unsure of how to do that. The attach files tab doesn't have the .xls extension available... not sure if it will allow me to post my work so other can see it. I would be happy to email it to anyone that is willing to help....

Please help... I'm so close to finishing this thing.

Help from Brazil
Good afternoon FluidPowerOne.

A) "...I am currently using Excel 2003, because my company refuses to update. Have I reached the limits of this Excel version?..."
Answer: No. The limit for Excel 2003 is: 65,536 rows; 256 columns. Total of 16,777,216 cells

B) "...The attach files tab doesn't have the .xls extension available... not sure if it will allow me to post my work so other can see it..."
Answer: You must ZIP your file before attach it to the forum

Attach your file here and explain exactly what you want.
Put a clear example about the desirable result.

Remember, you are a master of your data but we don´t know nothing about them. Then be so clear as possible on your explanation.

I believe that you will receive help sooner.
Have a nice Day.

Thanks for the advice. This is my first time joining a forum of a technical nature. So here goes... This work book is broken down like this...

Sheet "H-DATA' contains the assembly number and model code for a number of components. Each component has an assembly number that represents a part number. Behind that part number is a long description of the part in the form of a model code. This model code tells everything about the part much like the VIN number would on a vehicle. Each one is unique. There are literally millions of possible model codes for a given product. To the right on the sheet named "H-DATA' there are cells containing detailed descriptions of each of the features of each motor. These features come from mid/index/match formulas that pull information from the first page in the workbook named 'H-MC'. Ultimately the goal is to have multiple product pages like this one that can be dumped into a database.

Here's the problem... I made a sheet called 'Look-up Sheet' that a user can input an assembly number into in order to retrieve feature results contained in the page 'H-DATA'. I hit a road block on the very first part of the sheet. In the assembly numbers there are often dashes, slashes, and dots that separate parts of the model code from others for different reasons. In the 'H-DATA' sheet I wrote a column specifically to show the assembly number of the part and one for the model code of the part with out any of these delimiters purely for searching purposes.

Looking at sheet 'Look-up Sheet' you'll find the aggravating "#N/A" that I am speaking of. I want the user to input the numbers that they see stamped right on the part into the bordered cell and then have the remainder of the cells auto populate with the corresponding details about the features.

I attached the .zip file as requested. I had to cut and paste some of the data because the original workbook is far to large to post on here. If needed I can email it to someone to get a better breadth of the project. The same problem persists even in the smaller workbook that I've attached.

I suppose that I should add that I since I had to chop down the workbook to fit for uploading you won't find the 'H-MC' sheet that I spoke of previously nor any of the information "to the right" of the assembly number and model codes in sheet 'H-DATA'.

None the less, the problem persists even without the additional data.

Help from Brazil
Good Morning FluidPowerOne.

A) "...Looking at sheet 'Look-up Sheet' you'll find the aggravating "#N/A" that I am speaking of..."
Answer: The main question is: You´re comparing a NUMBER format 'Look-up Sheet' F2 to a TEXT format 'H-DATA' Column D

Try to use this one at: 'Look-up Sheet' D4
Before: =INDEX('H-DATA'!C2: D300,MATCH('Look-up Sheet'!F2,'H-DATA'!D2: D300,0),1)
Now...: =INDEX('H-DATA'!C2: D300,MATCH(TEXT('Look-up Sheet'!F2,0),'H-DATA'!D2: D300,0),1)

Tell me if it worked for you.

Have a nice Day.

Worked perfectly. I had a similar problem with another formula in this workbook that was solved by using the properties of the cells menu to change the format from number to text. I tried that in this scenario as well and kept getting the #N/A error.

Why would that not work in this instance and require the formula to format the data?

Help from Brazil
Good Evening FluidPowerOne.

First of all, remember that you obtained 'H-DATA Sheet' D column using a TEXT formula.

A) "...another formula in this workbook that was solved by using the properties of the cells menu to change the format from number to text. I tried that in this scenario as well and kept getting the #N/A error..."
Answer: If you prefer you can work with Cell Formats too.

Try to do this at: 'Look-up Sheet' F2
a) Change cell format from Number/General to Text
b) Clear the cell

Now, type any code like: 1011001009 and tell me if worked as you desire.

Note:
Particularly, I always prefer use a number as number and a text as text to avoid big problems in the future when the spreadsheet can be bigger and more complex.

Have a nice Day.