How can I use any one of multiple codes to lookup a product name?

I have a database here at work that has one worksheet, called Legend, which has product names listed on the same row as series of various product codes for that product name. On another worksheet ,called Input, I am trying to find a formula or function that I can use where I can enter any ONE of those multiple product codes, and have Excel "find" the product code and return the product name in the next cell to the right. Of course the product name is always in the same column, but the product codes are scattered throughout various columns.

It seems like this should be possible in Excel 2007, because I can go to the Legend worksheet and do 'ctrl-F' enter my code and it will find it anywhere on that worksheet. From there all I am asking it to do is give me the text that is in column A on whatever that same row is.

Please let me know if you have a solution in Excel, or if I should be using Access or even a more powerful database for this operation. Thanks for any help you might be able to provide.

Do you have an example?

I have attached a mock example to show the issue that I am getting at. The current state of the real workbook is in massive disarray from my predecessor. But on my example you can see that in the Legend sheet, I have product names listed with various codes listed along the same row. In the Input sheet I have one column for the a the product code and I want the product name to populate in that column based on the code that I enter from the Legend sheet.

If everything is in a neat order like it is on the sheet you sent, could you not take each UPC/Code and combine them all in one column through copy/paste, one UPC/Code column at a time, and copy the column "product name" on the right of each code to match?

Then the vlookup would be a piece of cake.

Thanks Bob for your many replies...I do appreciate the help. I have thought of that option but the concept or idea was to be able to have each product on one row so that continually more codes could be added in the column to the right. In reality some products have many many codes, while other products just have one or two codes. In addition to that there are far more than 5 codes.....some of the legends have thousands of products.

I had thought of trying to use a combination of functions. Something such as using both Index and Match together. So that the match function would identify the row for the the searched code and the the index function could return the text in column A of the 'match'ed row.

Any other help?

Try this. On the Input sheet create a column for each UPC/Code. Do an iferror(vlookup for each with the iferror returning as blank "".

Highlight all the cells and copy paste special. With all the cells highlighted still press F5. Go to Special, select blanks, press ok. All blanks should be highlight now. Hold shift and press the - key. Have it shift all cells to the left.

It's more or less a makeshift fix, but that's all I can think of for a quick solution.