Legend?

I need to make a formula that will automatically populate qty's from a legend on sheet 1 into sheet 2 when I insert a cost code


ex: if A1(sheet 2)=1 then insert qty from B1(sheet 1)
if A1(sheet 2) = 2 then insert qty from C1 (sheet 1)
if A1(sheet 2) = 3 then insert qty from D1 (sheet 1)
and so on
and so on

What would be the formula to perform the task above?

The table that I have is pretty extensive and this formula will be placed in a number of diff cells, but if I have the base formula I can just change the cells addresses

Maybe I explained the formula incorrectly...

If A28 (sheet 2) =1, Then B28 (sheet 2) = qty in D3 (sheet 1)
If A28 (sheet 2) =2, Then B28 (sheet 2) = qty in D4 (sheet 1)
If A28 (sheet 2) =3, Then B28 (sheet 2) = qty in D5 (sheet 1)
and so on
and so on

Thats sweet!! Thx tiger.

@ bamma86 - you got away with a poor thread title this time!

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.