ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   Vlookup with an Arrayformula.... HELP! (https://www.excelbanter.com/excel-discussion-misc-queries/446133-vlookup-arrayformula-help.html)

Vitamin J

Vlookup with an Arrayformula.... HELP!
 
1 Attachment(s)
Ok... so here's my issue... I've created a simple form in Google docs for inventory tracking purposes that I can use a scanner with. It's just a way for employees to track what they use for supplies and what pallet they took them from.

Every item has a unique part number. I created a reference sheet called "Items" that has each item number listed in Column A and the item 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
Quantity
Unit Size

Once they enter the following info they click "Submit" and the data is 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 in C on the Items list and display the Item Description for that number in H. Seems pretty simple using Vlookup but the problem I'm having is that every time info is submitted from the form a new row is created in the 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 with 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 description for the Item number in Cell C2. I need it to display the description 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 H4 = Bale Wire - 12 GAGE - PAPER BALER)

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


All times are GMT +1. The time now is 04:46 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com