Vlookup in front of an array

Hey guys,

I have an array equation that returns a unique list of customer names, that references a part # in cell 'U2' that i need to copy down about 150 rows (to U27) and over 25 columns (to AU). The equation returns a unique list of customer names that purchase each product. The equation works fine when it stands alone:


{=INDEX('sheet1'!$B$2:$B$30000,SMALL(IF(('sheet1'!$U2='sheet1'!$D$2:$D$30000)*(COUNTIF($U2:U2,'sheet1'!$B$2:$B$30000)=0),ROW('sheet1'!$D$2:$D$30000)-MIN(ROW('sheet1'!$D$2:$D$30000))+1,""),1))}


However, when I add a vlookup in front of it and add the necessary vlookup info on the back, when I pull the equation over to the right,the first cell pulls the correct vlookup info, but the rest simply repeat the first returned value as shown:


{=VLOOKUP(INDEX('sheet1'!$B$2:$B$30000,SMALL(IF(('sheet1'!$U2='sheet1'!$D$2:$D$30000)*(COUNTIF($U2:U2,'sheet1'!$B$2:$B$30000)=0),ROW('sheet1'!$D$2:$D$30000)-MIN(ROW('sheet1'!$D$2:$D$30000))+1,""),1)),'sheet2'!$B$2:$K$510,10,0)}


If anyone knows a slight tweak or a way to make this work i would appreciate it.

Ryan

Hey

I see what you mean about muliple matches, as long as they are unique, but im not sure thats my problem. Right now when I enter the equation in cell 'U3' and drag over 3 cells to the right, I will get 3 unique names based on the data, (A,B,C):

Part # Cust A Cust B Cust C
U2 U3 U4 U5
58017 A B C

However when I throw the vlookup on the front of the array formula, instead of returning a vlookup value for each cust (A,B,C), it seems to constrain the formula to repeat the first matched vlookup. Instead of looking like the example below:

Part # Cust A Cust B Cust C
U2 U3 U4 U5
58017 Email A Email B Email C

It returns the same email for each cell as in cell 'U3' as shown:

Part # Cust A Cust B Cust C
U2 U3 U4 U5
58017 Email A Email A Email A

If you can think of a way to for the formula to perform the array and then vlookup the result independently in each cell I would greatly appreaciate it.

Let me know and thanks in advance.

Ry