Finding multiple rows from table in external workbook with criteria in 2 columns

[GeorgeThalassinos]

I have a workbook where the user inputs an Item Code and I want to present them with a list of LOT numbers (from a Table existing in another workbook) to choose from. The list will then be used in a list box so I would like the result in an array.

So my input is an Item Code (I am not sure if it is a string or a number yet) and with that I need to select all the LOT numbers in the external table where ItemCode=InputValue (variable, first criterion) and Status = 5 (fixed value, second criterion) and load an array with the results from the LOTnumber column. In the following example this would be {10005, 10007}.

The table is an Excel 2010 TABLE object

Book Test.xlsm range("C_INPUTVALUE").value = 4007221031383 Book PROD.xlsm Table PROD.xlsm!T_PRODUCTION[#Data] fld1 Itemcode Itemdesc LOTnumber Status q 28030100 <colgroup><col></colgroup><tbody> </tbody> item1 <colgroup><col></colgroup><tbody> </tbody> 10001 <colgroup><col></colgroup><tbody> </tbody> 8 <colgroup><col></colgroup><tbody> </tbody> q 4007221031383 item2 10004 6 q 4007221031383 item2 10002 3 q 4007221031383 item2 10005 5 wt 4007221031383 item2 10007 5 ret 4007221031383 item2 10003 9 34 28030100 item1 10008 1 22 4007221031383 item2 10006 1 4 4007221031383 item2 10009 1 <tbody> </tbody>

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

I know how to do this with loops but I would like to find a way using objects. I am just starting with them but I see that there is an advantage there.

Any help will be greatly appreciated.