VLookup from SQL Database

Databasehelp

New Member
Joined
Mar 25, 2019
Messages
1
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.
Item_IDQtyWork OrderItem Description
0PW10388
30-3151PW10388Item 2
30-6081PW10388Item 3
30-6101PW10388Item 4
30-6122PW10388Item 5
30-6114PW10388Item 6
30-6134PW10388Item 7
P30-50518PW10388Item 8
30-3022PW10388Item 9
30-4034PW10388Item 10
30-6162PW10388Item 11
60-1023PW10389Item 12
P30-3153PW10389Item 13
30-6093PW10389Item 14
30-613B3PW10389Item 15
30-511B1PW10389Item 16
30-6081PW10389Item 17
30-6101PW10389Item 18
30-3151PW10389Item 19
30-6081PW10389Item 20
30-6101PW10389Item 21

<colgroup><col span="2"><col><col></colgroup><tbody>
</tbody>

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))
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.

<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;">
</tbody>


<colgroup><col span="2"><col><col></colgroup><tbody>
</tbody>
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

jmacleary

Well-known Member
Joined
Oct 5, 2015
Messages
1,056
Office Version
  1. 365
  2. 2007
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,610
Messages
5,625,795
Members
416,138
Latest member
Pizzaman22

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top