Vlookup with an Arrayformula.... HELP!


Vitamin J

Ok... so here's my issue... I've created a simple form in Google doc
for inventory tracking purposes that I can use a scanner with. It'
just a way for employees to track what they use for supplies and wha
pallet they took them from.

Every item has a unique part number. I created a reference sheet calle
"Items" that has each item number listed in Column A and the ite
description listed next to the item number in Column B.

When someone uses the form they enter:

Their name
Contract Number
Item Number
Pallet Number
Unit Size

Once they enter the following info they click "Submit" and the data i
entered into a spreadsheet like this:

Column A = Timestamp
Column B = Name
Column C = Contract Number
Column D = Item Number
Column E = Pallet Number
Column F = Quantity
Column G = Unit Size
Column H = Item Description (Formula Entered here)

What I want the spreadsheet to do is look up the Item number entered i
C on the Items list and display the Item Description for that number i
H. Seems pretty simple using Vlookup but the problem I'm having is tha
every time info is submitted from the form a new row is created in th
spreadsheet so the formula gets moved down to the row below the data.
I've used an array formula before on a similar project but not wit
Vlookup. The formula I've come up with so far is this:

=Arrayformula(If(D2:D=""," ",(VLOOKUP(D2:D,Items!$A1:$B200,2,0))))

In the google doc it works but it always displays the item descriptio
for the Item number in Cell C2. I need it to display the descriptio
for the item number that's in the same row as the formula (e.g. if C2
105874 then H2 = Air Pillows (Sealed Air Select) If C4 = 100645 then H
= Bale Wire - 12 GAGE - PAPER BALER)

I've uploaded a zip with the spreadsheet... Help me please

|Filename: Consumable Material Tracker.zip
|Download: http://www.excelbanter.com/attachment.php?attachmentid=390


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads