Conditional Formatting/Multi Use of Formulas

Good morning to all. I am new to the Excel Forum and am so HAPPY your help is available. I am having an issue with formulas an understanding which formula to use. I am currently using MS Pro 2003 Excel. What I have is an Inventory list of about 1180 items on PAGE 2. On Page 1 I have our Sales Form. My attempt to lessen the blow of "user error" and cost control. What I want to do is have the ability to input our UPC number for in one cell and then the rest of the information to automatically fill in on the ROW. (i.e. item code, color, description, cost). I have tried a VLookup, IF/THEN and it's just not working. I did create a database from PAGE 2 of the 1180 items. Any idea's to help with this dilemma...

Best regards,

IsleTurbo

EDIT: A little slow today...lol

Vlookup should most likely work, if done correctly.

Can you post a small sample workbook depicting your set up?

Click the paperclip icon in the Reply box to upload a sample.

Here is a copy. I did delete our company information but you can get an idea of what I mean.

EDIT: I removed my attachment for privacy, but I can reattach something similar for reference so others can solve the same issue.

So you want to enter the UPC # in column D and get the information in the columns to the left and right?

If so, then you would need to use INDEX/MATCH for best results.

Try in B23:

=IF($D23="","",INDEX(Price!B:B,MATCH($D23,Price!$H:$H,0)))

copied down

Then copy that formula into other cells in row 23 and adjust only the first range after the INDEX (red) to correspond to the column on the other sheet to get the info from.

Tip: You could move the UPC code column on the Price sheet to be first column... then you can easily use VLOOKUP....

You (or someone else) has set a Data Validation Rule on the Quantity column to force user to enter a whole number between 6 and 9999.

If you want to remove that select the column A range and go to Data|Validation and select Clear All.

I felt silly after posting my last one. I forgot I had the Validation on. THANK YOU SO MUCH for your help. It worked beautifully. I wish I could shake your hand or give you a big hug. You are full of awesomeness. I am going to make this SOLVED. Thanks again.

EDIT: I can't seem to find where the PREFIX Dropdown to select SOLVED. Can you please mark this solved for me.
EDITT: I see I had to go to my original post to do this. Slow day.