compare and label question

[Hotwater]

OK, so I’m going cross eyed trying to figure this out. It seems so obvious or common that it would be everywhere, but I’m clearly not finding it. Please help if you can.

I have two excel sheets, the first sheet has a bunch of data and in particular one column that has part numbers. The second sheet has columns each with heading that that describes the part numbers, like a column called “Washers” with the list of part numbers below of all the washers and so on. For a total of 5 categories.

I’d like to have a formula or script for a blank column to the first sheet that automatically compares the part number in the first column with the data in the second sheet and populates the category, e.g. “washers”. I’d like to be able to add and subtract to the second sheet as the part numbers change or get deleted. I would also like the logic to say “N/A” or something similar if it can’t find a matching category.

Thanks in advance!

 

[pbornemeier]

Thanks to mikerickson's post from: http://www.mrexcel.com/forum/excel-questions/516742-how-return-results-position-2d-array.html

Set up your workbook this way:

Column A:E of the PartNumbers worksheet contain the Description/PartNumbers database, with the first row of each column containing the description for the partnumbers below them (Washers, Bolts, et al.)
Create a named range for this database using an offset formula so the named range dimensions will adjust as columns/rows are added/deleted
DataRange = =OFFSET(PartNumbers!$A$1,0,0,COUNTA(PartNumbers!$A:$A),COUNTA(PartNumbers!$1:$1))

Create the named range as follows:

'Formulas | Name Manager | New ....
'Name =        DataRange
'Scope =       Workbook
'Refers to =   =OFFSET(PartNumbers!$A$1,0,0,COUNTA(PartNumbers!$A:$A),COUNTA(PartNumbers!$1:$1))

Column A of Sheet1 contains your partnumbers
Cell B1 of Sheet1 contains this formula: =INDIRECT("PartNumbers!" & ADDRESS(1,SUMPRODUCT(--(DataRange=A1)*COLUMN(DataRange)),1,1))
which should be copied down column B as far as required


It shows #VALUE! if a partnumber does not exist in the database range