Matching part of one cell to an array of other cells to display the corresponding cell

I'm trying to make a spreadsheet for corresponding part numbers to colors, and can't quite figure out the proper formula/syntax to use. I haven't used excel much at all over the past few years, so I'm pretty rusty.

Basically, if I've got a part number in cell A2 that contains something like this: RD110, where RD corresponds to a color indicator in Column A of a sheet I have named "Colors XY", and I want the solution of the formula to output the corresponding value in Column B of the sheet I have named "Colors XY". How can I do this?

I'd also like to be able to output that the first number in "110" corresponds to a finish. As in, 110 means gloss, and 210 means matte finish. Is this possible to do as well?

Thanks in advance for any help you can give me.

You can use a concantenate function with a vlookup for the left "RD" and a vlookup for the right "110" so data in A2 (RD110) would return "Red Gloss". The only problem is the corresponding finish numbers would have to be formatted as text (preceded by an apostrophe) in order to match it with the value in Cell A2. Your formula would look something like this:

=CONCATENATE(VLOOKUP(LEFT(A2,2),Sheet2!A1:B2,2,0)," ",(VLOOKUP(RIGHT(A2,3),Sheet3!A1:B2,2,0)))

This assumes Sheet 2 contains your color indicators and Sheet 3 contains your finish indicators. Hope this helps.

Originally Posted by Maureen405

You can use a concantenate function with a vlookup for the left "RD" and a vlookup for the right "110" so data in A2 (RD110) would return "Red Gloss". The only problem is the corresponding finish numbers would have to be formatted as text (preceded by an apostrophe) in order to match it with the value in Cell A2. Your formula would look something like this:

=CONCATENATE(VLOOKUP(LEFT(A2,2),Sheet2!A1:B2,2,0)," ",(VLOOKUP(RIGHT(A2,3),Sheet3!A1:B2,2,0)))

This assumes Sheet 2 contains your color indicators and Sheet 3 contains your finish indicators. Hope this helps.

Awesome! That works perfectly.

Thank you very much!