Quote Originally Posted by JBeaucaire View Post
Ok, a few tricks needed for this.

First I've installed a new function called VLOOKUPS. In short, it returns an array of answers the same way VLookup returns a single answer. It's used the same way a VLookup is, except there's only 3 parameters, not 4.

Next, we have to create a VLOOKUPS list for each of the items in your sheet H5:H25. I put these lists off to the right in columns O:AI. Each column is bringing back an array of matching Lot Numbers for each of the items in H5:H25.

Now, we need to create a Dynamic Named range for each of the lists in O:AI. I created the first two for you. If you open the Insert>Name>Define>ListH5 you will see that ListH5 is defined as:

=OFFSET('Cost Per Pound'!$O$1,0,0,MAX(IF(ISNA(MATCH("*",'Cost Per Pound'!$O:$O,-1)),1,MATCH("*",'Cost Per Pound'!$O:$O,-1)),IF(ISNA(MATCH(1E+306,'Cost Per Pound'!$O:$O)),1,MATCH(1E+306,'Cost Per Pound'!$O:$O))))

So, this formula makes a list of ONLY the cells in column O with values. Blanks are stripped away.

The formula for ListH6 is similar, it just changes the column references, like so:

=OFFSET('Cost Per Pound'!$P$1,0,0,MAX(IF(ISNA(MATCH("*",'Cost Per Pound'!$P:$P,-1)),1,MATCH("*",'Cost Per Pound'!$P:$P,-1)),IF(ISNA(MATCH(1E+306,'Cost Per Pound'!$P:$P)),1,MATCH(1E+306,'Cost Per Pound'!$P:$P))))

Last, in H5, I added the Data > Validation > List =ListH5.

The first two are done, you can do the remaining Dynamic Name Range definitions and Validation List links.

In I5, I also created the the INDEX(MATCH() formula to pull over the Common Name. Do the same thing for Inventory Available.
this is a huge help! i am curious, is there a quicker way to create the vlookups lists? i have about 4000 records that i need to go through. is there something that does that in your 'ArrayFunction.xls'?