Look up product information based on product number

[bigmac37]

What I want to accomplish essentially is when I type in a number in one cell (a1 for example under category product number) that the information about that product shows up in the other cells (b1 c1..... ) such as the name of product, location.

It would essentially be searching for the product number and information from another page where a range of information is with all the different product numbers

[6StringJazzer]

Moderator's note: Please take the time to review our rules. There aren't many, and they are all important. Rule #1 requires good titles. I have updated it for you this time because you are a new member. --6StringJazzer

This is almost certainly a VLOOKUP. If the sheet containing all the product information has the product numbers in column A, and the product names in column B, then your formula to retrieve the product name for a product number typed into A1 would be:

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

You would extrapolate for other fields.

I can't give you a more specific answer without a more specific explanation of your data. The easiest thing is to attach your file. Under the text box where you type your reply click the Go Advanced button. On the next screen scroll down and click on Manage Attachments, which will show a pop-up window to Select and Upload a file. Then close the window.

[Syrkrasi]

For future reference

It is recommended that you attach a sample file so that we could get a clear understanding of you request.

The attach file has a worksheet titled Data.
This worksheet is used as the referencing source, and each column is set as a dynamic name range.

* To view how the Dynamic Name Range works, click on Formulas > Name Manager


The other worksheet (titled Form) gathers the data from the Data worksheet

- Column A - Cateory Product #: This column uses a drop down list which captures the data in Data column A by using Data Validation

Calculations
- Column B - Product: =IFERROR(IF(ISBLANK($A3),"",INDEX(Product,MATCH($A3,CatProdNum,0))),"") *remember to press Shift + Ctrl + Enter
- Column C - Location: =IFERROR(IF(ISBLANK($A3),"",INDEX(Location,MATCH(1,($A3=CatProdNum)*($B3=Product),0))),"") *remember to press Shift + Ctrl + Enter

[bigmac37]

Still unable to get it to work. Here is the file. maybe it is easier to explain with the file.

[Special-K]

The account numbers in your Account sheet are text not numbers at all.
For VLOOKUP to work both the value you are looking up AND the table you are looking up need to be the SAME type of data.

Select the Account nuimbers in the Account
Format them as General.
Press F2 to edit each one and simply hit return, this will turn them into numbers

in Sheet1!D2
=VLOOKUP($C2,Accounts!$B$2:$E$15,COLUMN()-2,0)
and copy across as far as column F

Add other account numbers and copy the formulas down the columns for each account number you've entered.

[bigmac37]

Awesome. Thank you!

[bigmac37]

Is it then possible that if you put type an order on the "Orders" sheet. That that specific order is then also reflected on another page "Re-Orders".
It would then go under the specific week of the order and in the same row as the its respective account number. Take a look at the attached number. Hard to explain but maybe if you take a look it would be easy to see.

Example.
1. place order in "orders sheet"
2. that order is than reflected automatically in the "re-order" sheet under the same account number and respective week when order was made.

[Special-K]

You may need VBA for that and I'm no expert.

[bigmac37]

what is vba?

[6StringJazzer]

VBA is the Microsoft Office macro language. It Visual Basic for Applications.