# compare and label question

#### Hotwater

##### New Member
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.

### Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

#### pbornemeier

##### Well-known Member
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:
Code:
``````'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

Replies
4
Views
253
Replies
4
Views
2K
Replies
1
Views
236
Replies
2
Views
182
Replies
9
Views
283

1,195,655
Messages
6,010,939
Members
441,577
Latest member
Alonshow

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back