We manufacture hundreds of parts for multiple clients. In sheet1 there are
drop down menus to choose the client and the unique part description of the
parts that were produced. Sheet 2 contains a master list of part descriptions
and the corresponding part codes. I would like the part code to
automatically populate on Sheet1 once the corresponding part description has
been selected.

Sheet1: Choose clients and parts produced

Column A: Client B: Description C: Part Code
GE 5mm Bolt
GE 7mm Bolt
BMW 9mm Washer

Sheet 2: Master List

Column A: Client B: Description C: Part Code
GE 5mm Bolt INT5105
GE 6mm Bolt INT5106
GE 7mm Bolt INT5107
BMW 5mm Washer INT6005
BMW 6mm Washer INT6006
BMW 7mm Washer INT6007

Is it possible to lookup and match the part description and then return the
corresponding part code from Sheet2 to Sheet1?

Hope this makes sense.

Array function (copy this formula and enter in cell c2and press
ctrl+shift+enter
Index(Sheet2!c$1:c$500,match(1,(Sheet2!B$1:B$500=b 2)*(Sheet2!a$1:a$500=a2),0))

"Charlie510" wrote:

We manufacture hundreds of parts for multiple clients. In sheet1 there are
drop down menus to choose the client and the unique part description of the
parts that were produced. Sheet 2 contains a master list of part descriptions
and the corresponding part codes. I would like the part code to
automatically populate on Sheet1 once the corresponding part description has
been selected.

Sheet1: Choose clients and parts produced

Column A: Client B: Description C: Part Code
GE 5mm Bolt
GE 7mm Bolt
BMW 9mm Washer

Sheet 2: Master List

Column A: Client B: Description C: Part Code
GE 5mm Bolt INT5105
GE 6mm Bolt INT5106
GE 7mm Bolt INT5107
BMW 5mm Washer INT6005
BMW 6mm Washer INT6006
BMW 7mm Washer INT6007

Is it possible to lookup and match the part description and then return the
corresponding part code from Sheet2 to Sheet1?

Hope this makes sense.

Alright, taking into consideration that the part descriptions are unique I
ignored the client data entered the following formula which is resulting in
an error "#NA", and I'm not sure what I've missed.

=INDEX('Sheet2'!C$4:C$311,MATCH(1,('Sheet2'!B$4:B$ 311=B12),0))

Thanks

"N Harkawat" wrote:

Array function (copy this formula and enter in cell c2and press
ctrl+shift+enter
Index(Sheet2!c$1:c$500,match(1,(Sheet2!B$1:B$500=b 2)*(Sheet2!a$1:a$500=a2),0))

"Charlie510" wrote:

We manufacture hundreds of parts for multiple clients. In sheet1 there are
drop down menus to choose the client and the unique part description of the
parts that were produced. Sheet 2 contains a master list of part descriptions
and the corresponding part codes. I would like the part code to
automatically populate on Sheet1 once the corresponding part description has
been selected.

Sheet1: Choose clients and parts produced

Column A: Client B: Description C: Part Code
GE 5mm Bolt
GE 7mm Bolt
BMW 9mm Washer

Sheet 2: Master List

Column A: Client B: Description C: Part Code
GE 5mm Bolt INT5105
GE 6mm Bolt INT5106
GE 7mm Bolt INT5107
BMW 5mm Washer INT6005
BMW 6mm Washer INT6006
BMW 7mm Washer INT6007

Is it possible to lookup and match the part description and then return the
corresponding part code from Sheet2 to Sheet1?

Hope this makes sense.

Sorry, one more bit of info: the error "#NA" is occuring in the Row_Num field
of the INDEX function.

"N Harkawat" wrote:

Array function (copy this formula and enter in cell c2and press
ctrl+shift+enter
Index(Sheet2!c$1:c$500,match(1,(Sheet2!B$1:B$500=b 2)*(Sheet2!a$1:a$500=a2),0))

"Charlie510" wrote:

We manufacture hundreds of parts for multiple clients. In sheet1 there are
drop down menus to choose the client and the unique part description of the
parts that were produced. Sheet 2 contains a master list of part descriptions
and the corresponding part codes. I would like the part code to
automatically populate on Sheet1 once the corresponding part description has
been selected.

Sheet1: Choose clients and parts produced

Column A: Client B: Description C: Part Code
GE 5mm Bolt
GE 7mm Bolt
BMW 9mm Washer

Sheet 2: Master List

Column A: Client B: Description C: Part Code
GE 5mm Bolt INT5105
GE 6mm Bolt INT5106
GE 7mm Bolt INT5107
BMW 5mm Washer INT6005
BMW 6mm Washer INT6006
BMW 7mm Washer INT6007

Is it possible to lookup and match the part description and then return the
corresponding part code from Sheet2 to Sheet1?

Hope this makes sense.

If client data is unique then a simple vlookup should do:

=vlookup(b2,sheet2!b$1:c$500,2,0)


"Charlie510" wrote:

Sorry, one more bit of info: the error "#NA" is occuring in the Row_Num field
of the INDEX function.

"N Harkawat" wrote:

Array function (copy this formula and enter in cell c2and press
ctrl+shift+enter
Index(Sheet2!c$1:c$500,match(1,(Sheet2!B$1:B$500=b 2)*(Sheet2!a$1:a$500=a2),0))

"Charlie510" wrote:

We manufacture hundreds of parts for multiple clients. In sheet1 there are
drop down menus to choose the client and the unique part description of the
parts that were produced. Sheet 2 contains a master list of part descriptions
and the corresponding part codes. I would like the part code to
automatically populate on Sheet1 once the corresponding part description has
been selected.

Sheet1: Choose clients and parts produced

Column A: Client B: Description C: Part Code
GE 5mm Bolt
GE 7mm Bolt
BMW 9mm Washer

Sheet 2: Master List

Column A: Client B: Description C: Part Code
GE 5mm Bolt INT5105
GE 6mm Bolt INT5106
GE 7mm Bolt INT5107
BMW 5mm Washer INT6005
BMW 6mm Washer INT6006
BMW 7mm Washer INT6007

Is it possible to lookup and match the part description and then return the
corresponding part code from Sheet2 to Sheet1?

Hope this makes sense.

For some reason I'm getting a "#REF!" errow; however, when I point the
formula to Column 1 it returns the part description just fine:

=VLOOKUP(B2,'Sheet2'!B$4:B$311,1,0)

I'm not sure why it won't return the part code from the column next door????


"N Harkawat" wrote:

If client data is unique then a simple vlookup should do:

=vlookup(b2,sheet2!b$1:c$500,2,0)


"Charlie510" wrote:

Sorry, one more bit of info: the error "#NA" is occuring in the Row_Num field
of the INDEX function.

"N Harkawat" wrote:

Array function (copy this formula and enter in cell c2and press
ctrl+shift+enter
Index(Sheet2!c$1:c$500,match(1,(Sheet2!B$1:B$500=b 2)*(Sheet2!a$1:a$500=a2),0))

"Charlie510" wrote:

We manufacture hundreds of parts for multiple clients. In sheet1 there are
drop down menus to choose the client and the unique part description of the
parts that were produced. Sheet 2 contains a master list of part descriptions
and the corresponding part codes. I would like the part code to
automatically populate on Sheet1 once the corresponding part description has
been selected.

Sheet1: Choose clients and parts produced

Column A: Client B: Description C: Part Code
GE 5mm Bolt
GE 7mm Bolt
BMW 9mm Washer

Sheet 2: Master List

Column A: Client B: Description C: Part Code
GE 5mm Bolt INT5105
GE 6mm Bolt INT5106
GE 7mm Bolt INT5107
BMW 5mm Washer INT6005
BMW 6mm Washer INT6006
BMW 7mm Washer INT6007

Is it possible to lookup and match the part description and then return the
corresponding part code from Sheet2 to Sheet1?

Hope this makes sense.

Thanks so much for your help. The "#REF!" error was due to the fact that I
wasn't including column c in the array. It works beautifully.

"N Harkawat" wrote:

If client data is unique then a simple vlookup should do:

=vlookup(b2,sheet2!b$1:c$500,2,0)


"Charlie510" wrote:

Sorry, one more bit of info: the error "#NA" is occuring in the Row_Num field
of the INDEX function.

"N Harkawat" wrote:

Array function (copy this formula and enter in cell c2and press
ctrl+shift+enter
Index(Sheet2!c$1:c$500,match(1,(Sheet2!B$1:B$500=b 2)*(Sheet2!a$1:a$500=a2),0))

"Charlie510" wrote:

We manufacture hundreds of parts for multiple clients. In sheet1 there are
drop down menus to choose the client and the unique part description of the
parts that were produced. Sheet 2 contains a master list of part descriptions
and the corresponding part codes. I would like the part code to
automatically populate on Sheet1 once the corresponding part description has
been selected.

Sheet1: Choose clients and parts produced

Column A: Client B: Description C: Part Code
GE 5mm Bolt
GE 7mm Bolt
BMW 9mm Washer

Sheet 2: Master List

Column A: Client B: Description C: Part Code
GE 5mm Bolt INT5105
GE 6mm Bolt INT5106
GE 7mm Bolt INT5107
BMW 5mm Washer INT6005
BMW 6mm Washer INT6006
BMW 7mm Washer INT6007

Is it possible to lookup and match the part description and then return the
corresponding part code from Sheet2 to Sheet1?

Hope this makes sense.