Need To Input Data Automatically In One Cell Based On Data In Cell Next To It

I am trying to create a workbook template that I can create order checklists from. Column A would have the product size, column B would have the product number. I have set up each cell in column A with a drop-down box to make it easier to enter the sizes, as there are 48 of them. Now what I want to do is have excel automatically insert the product number into the adjacent cell in column B. I've been looking around on this site trying to find a way to do this, but nothing has worked so far. I tried the IF function, but I'm sure you know it is limited to 7 nestings. I even tried the workaround, but it's proving to be a little too complex. I'm uploading a copy of my workbook so you can see what I've been trying. The sizes and their matching product numbers are on sheet 2.

I would appreciate any help.

First, thanks for the quick reply. This works, but the farther down column A you made a selection, the larger the size you have to choose.

=VLOOKUP(A4,Sheet2!A2:C50,2,FALSE)

As you went down column A, the formula changed as follows...

=VLOOKUP(A4,Sheet2!A2:C50,2,FALSE)
=VLOOKUP(A5,Sheet2!A3:C51,2,FALSE)
=VLOOKUP(A6,Sheet2!A4:C52,2,FALSE)
=VLOOKUP(A7,Sheet2!A5:C50,2,FALSE)

I changed it to the following, plus I dropped it down to B column, on the lookup...

=VLOOKUP(A4,Sheet2!$A$2:$B$50,2,FALSE)

Now as you drag it down, only A4 changes...

=VLOOKUP(A4,Sheet2!$A$2:$B$50,2,FALSE)
=VLOOKUP(A5,Sheet2!$A$2:$B$50,2,FALSE)
=VLOOKUP(A6,Sheet2!$A$2:$B$50,2,FALSE)
=VLOOKUP(A7,Sheet2!$A$2:$B$50,2,FALSE)

Now, if you can tell me how to get rid of the #N/A in the cell when nothing is selected in A column, it will be perfect.

I am attaching the new workbook.

Ok, I found the answer....

=IF(ISNA(VLOOKUP(A4,Sheet2!$A$2:$B$50,2,FALSE))=TRUE,"",VLOOKUP(A4,Sheet2!$A$2:$B$50,2,FALSE))

The double quotes after TRUE will make the cell blank, or will say whatever you want, as long as you put it between the quotes.

I'm uploading the new workbook.

Thanks for your help Dunc3142.