VLookup from SQL Database

[Databasehelp]

Hi All,

What I'm trying to do is take from a SQL Database that we have in our system to populate an external material sheet. That being said, here is a sample of what I'm dealing with.
[TABLE="width: 489"]
<colgroup><col span="2"><col><col></colgroup><tbody>[TR]
[TD]Item_ID[/TD]
[TD]Qty[/TD]
[TD]Work Order[/TD]
[TD]Item Description[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]0[/TD]
[TD]PW10388[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]30-315[/TD]
[TD]1[/TD]
[TD]PW10388[/TD]
[TD]Item 2[/TD]
[/TR]
[TR]
[TD]30-608[/TD]
[TD]1[/TD]
[TD]PW10388[/TD]
[TD]Item 3[/TD]
[/TR]
[TR]
[TD]30-610[/TD]
[TD]1[/TD]
[TD]PW10388[/TD]
[TD]Item 4[/TD]
[/TR]
[TR]
[TD]30-612[/TD]
[TD]2[/TD]
[TD]PW10388[/TD]
[TD]Item 5[/TD]
[/TR]
[TR]
[TD]30-611[/TD]
[TD]4[/TD]
[TD]PW10388[/TD]
[TD]Item 6[/TD]
[/TR]
[TR]
[TD]30-613[/TD]
[TD]4[/TD]
[TD]PW10388[/TD]
[TD]Item 7[/TD]
[/TR]
[TR]
[TD]P30-505[/TD]
[TD]18[/TD]
[TD]PW10388[/TD]
[TD]Item 8[/TD]
[/TR]
[TR]
[TD]30-302[/TD]
[TD]2[/TD]
[TD]PW10388[/TD]
[TD]Item 9[/TD]
[/TR]
[TR]
[TD]30-403[/TD]
[TD]4[/TD]
[TD]PW10388[/TD]
[TD]Item 10[/TD]
[/TR]
[TR]
[TD]30-616[/TD]
[TD]2[/TD]
[TD]PW10388[/TD]
[TD]Item 11[/TD]
[/TR]
[TR]
[TD]60-102[/TD]
[TD]3[/TD]
[TD]PW10389[/TD]
[TD]Item 12[/TD]
[/TR]
[TR]
[TD]P30-315[/TD]
[TD]3[/TD]
[TD]PW10389[/TD]
[TD]Item 13[/TD]
[/TR]
[TR]
[TD]30-609[/TD]
[TD]3[/TD]
[TD]PW10389[/TD]
[TD]Item 14[/TD]
[/TR]
[TR]
[TD]30-613B[/TD]
[TD]3[/TD]
[TD]PW10389[/TD]
[TD]Item 15[/TD]
[/TR]
[TR]
[TD]30-511B[/TD]
[TD]1[/TD]
[TD]PW10389[/TD]
[TD]Item 16[/TD]
[/TR]
[TR]
[TD]30-608[/TD]
[TD]1[/TD]
[TD]PW10389[/TD]
[TD]Item 17[/TD]
[/TR]
[TR]
[TD]30-610[/TD]
[TD]1[/TD]
[TD]PW10389[/TD]
[TD]Item 18[/TD]
[/TR]
[TR]
[TD]30-315[/TD]
[TD]1[/TD]
[TD]PW10389[/TD]
[TD]Item 19[/TD]
[/TR]
[TR]
[TD]30-608[/TD]
[TD]1[/TD]
[TD]PW10389[/TD]
[TD]Item 20[/TD]
[/TR]
[TR]
[TD]30-610[/TD]
[TD]1[/TD]
[TD]PW10389[/TD]
[TD]Item 21[/TD]
[/TR]
</tbody>[/TABLE]

What I want to do is type in the work order "PW10388" and for the material sheet to populate any work order with that number to populate the rest of the information (Item ID, Qty and description in different columns). What I'm trying to do is use a VLookup which would look to the right and find that item. I'm not sure if that is being stopped since the first item qty is always 0 in the database (along with a description that has no description, essentially 0). It will keep reading the same output as 0 or N/A.

When I use a index/match it has a problem reading the same work order twice and tends to look at the smallest number, when in fact I need every item to be populated. I use the entire column as the formula opposed to extending as set of value (e2:e999999) As this will be used for a while and our database is already getting up to that number quickly.

=INDEX(Item Description,MATCH(A3, WorkOrder,0))
[TABLE="width: 489"]
<tbody style="background-attachment: scroll; background-clip: border-box; background-color: transparent; background-image: none; background-origin: padding-box; background-position-x: 0%; background-position-y: 0%; background-repeat: repeat; background-size: auto; color: rgb(34, 34, 34); font-family: Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif; font-size: 13px; font-size-adjust: none; font-stretch: 100%; font-style: normal; font-variant: normal; font-weight: 400; line-height: normal; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;">[TR="bgcolor: transparent"]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: transparent"]
[TD]Item Description as seen above in table

A3 is the work order number that I type on the material list.

In my mind it should grab from the work order that I typed in. Look for any match of that work order and spit out the description (and hopefully the Quantity, labelled Qty). Again, what i'm running into is that there are multiple of the same number and it keeps taking the top number. We also have a separate database that I believe I can setup with item description and item id's so that when I populate the description, the Item Id will automatically populate as well.

If anybody can possibly steer me in the right direction, that would be greatly appreciated.
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 489"]
<colgroup><col span="2"><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[jmacleary]

Hi there. You say that the database is growing rapidly. Why not modify your sql query to take your input work order number and only return data that matches. You could also add a criterion to the query to only return non-zero quantities.