Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Excel Formula matching data 1

Status
Not open for further replies.
Apr 26, 2005
29
US
Hello,

I'll start with a brief synopsis of what I am trying to accomplish.

I have a spread sheet of products which contains information such as the item number, description, colors, quantity position, and a picture as an example for the customer. The qty position field needs to be populated
with the quantity available from another sheet. The first sheet is laid out like below. I'll refer to this worksheet as sheet 1.

Picture Description Item Number Color Qty Position
Frame 100 series 101 Black
Frame 100 series 102 Red
Frame 100 series 103 Brown
Frame 200 series 201 Black
Frame 200 series 202 Red
Frame 200 series 203 Brown

On a seperate worksheet, call it sheet 2, in the same spreadsheet, I have all of the possible item numbers and their available quantities in two seperate columns. What I need to have happen is the qty position on sheet 1 to be populated with the available quantity from sheet 2 if the item number on sheet 1 matches the item number on sheet 2.

any help would be greatly appreciated.

Thanks,
Mike
 
Assuming the table containing the item numbers and quantities is in sheet2!A1:B20, with item numbers in col A and quantities in col B.

Also assuming that the first item number in Sheet1 is in C1...



=VLOOKUP(C1,Sheet2!$A$1:$B$20,2,FALSE)

Lilliabeth
-Why use a big word when a diminutive one will do?-
 
Thank you lilliabeth, that did the job. The one thing I'm confused about though is the "2" before the false statement. What does the "2" designate?
 
The 2 tells Excel that the value we want returned (Quantity) is in the second column of the lookup table (Sheet2!A1:B20).

Lilliabeth
-Why use a big word when a diminutive one will do?-
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top