Populate cell based on drop down menu selection

I am trying to create a sales order form and need certain cells populated based on what was selected in a drop down menu.

example:

in A1, I need to auto populate a sku for a product. In B1 I have a drop down menu with four colors. Each color has a different sku. I need cell A1 to read 1234BK if "Black" is selected in B1, 1234OR if "Orange" is selected in B1, etc.

The pull down menu will not have the actual sku, but instead a list of colors.

Thanks in advance!

Where does your dropdown data come from? A range of cells? A comma-delimited list? A named range? If the data is listed in some cells, do you have corresponding SKUs to go with them?

If your data is nowhere on the worksheet to reference, try either of these:

=CHOOSE(MATCH(B1,{"Orange";"Black";"Blue";"Yellow";"White";"Red";"Green";"Purple"},),"1234OR","1234BK","1234BL","1234YL","1234WH","1234RE","1234GR","1234PR")

or

=INDEX({"1234OR","1234BK","1234BL","1234YL","1234WH","1234RE","1234GR","1234PR"},MATCH(B1,{"Orange","Black","Blue","Yellow","White","Red","Green","Purple"},))


Simply edit/add/remove colors and SKUs as needed. (Note the slight differences in syntax between the 2 formulas. Most notably, semi-colons vs. commas.)


If your data exists somewhere on the worksheet, a VLOOKUP will be your go-to function.

Posting a sample of your data would be easier for me to help you. Assuming your colors are in the range C1:C10 and the corresponding SKUs are in D1:D10, use this in cell A1:

=VLOOKUP(B1,C1:D10,2,0)

Adjust your ranges as needed.