Cross Reference Part Numbers

I am trying to build a cross reference database for all of our part numbers. Few questions first.

The database is setup similar to this. The first column (A) is the internal part number and the manufacturer part numbers are columns (B-K). The list right now is roughly 1000 rows long. The name of the sheet is Database.

Int Part# | Man1 | Man 2 | Man 3 | --> Man 10
1 MEM-1 | 123 | 1-24 | 42-9 | SCK146
2 MEM-1 | 345 | 2-24 | 43-9 | SCK147
3 MEM-1 | 678 | 3-24 | 44-9 | SCK148
4 MEM-1 | 910 | 4-24 | 45-9 | SCK149
5 MEM-1 | 111 | 5-24 | 46-9 | SCK150

On a seperate sheet named SearchSheet I need to be able to copy and paste a column of at least 50 part numbers to search the database and return the Internal Part number value for it found in the database. It would be ideal if I could return ALL of the values. What I mean is if I search for the value 45-9 exactly it will return MEM-1 | 123 | 1-24 | 42-9 | SCK146 in columns (B-L) of the same row as the part I searched for.

anyone have some ideas?

ANY tips?

bueller? bueller?

Just to clarify what you are looking for, do you only want the first row of information on Mem-1 to show up, or are you looking for all rows?

Originally Posted by Chuck N

I am trying to build a cross reference database for all of our part numbers. Few questions first.

The database is setup similar to this. The first column (A) is the internal part number and the manufacturer part numbers are columns (B-K). The list right now is roughly 1000 rows long. The name of the sheet is Database.

Int Part# | Man1 | Man 2 | Man 3 | --> Man 10
1 MEM-1 | 123 | 1-24 | 42-9 | SCK146
2 MEM-1 | 345 | 2-24 | 43-9 | SCK147
3 MEM-1 | 678 | 3-24 | 44-9 | SCK148
4 MEM-1 | 910 | 4-24 | 45-9 | SCK149
5 MEM-1 | 111 | 5-24 | 46-9 | SCK150

On a seperate sheet named SearchSheet I need to be able to copy and paste a column of at least 50 part numbers to search the database and return the Internal Part number value for it found in the database. It would be ideal if I could return ALL of the values. What I mean is if I search for the value 45-9 exactly it will return MEM-1 | 123 | 1-24 | 42-9 | SCK146 in columns (B-L) of the same row as the part I searched for.

With your table above in Sheet2, and your part numbers in column A, in cell B1 put

=OFFSET(Sheet2!A1,MATCH(A1,Sheet2!D$1:D$65535,0)-1,0)

and formula-fill that down your 50 parts.

for following columns, offset the column by +1, ie

=OFFSET(Sheet2!A1,MATCH(A1,Sheet2!D$1:D$65535,0)-1,1)
=OFFSET(Sheet2!A1,MATCH(A1,Sheet2!D$1:D$65535,0)-1,2)
=OFFSET(Sheet2!A1,MATCH(A1,Sheet2!D$1:D$65535,0)-1,4)

to get columns B C and E etc

hth
---
note, formula was tested on Sheet2! but using B1:B4 - hopefully the minor change was correct.
---