Neall said:
Now that I have my vlookup working I now have a new situation
I have a customer number which I use my vlookup to get data such as
Cust name
renewal date
product code
product description
Now the new problem is if I have one customer ID that has 5 different
products, how can I get all 5 products into my result and each in a different
cell. each customer will have no more than at the most 10 part numbers per
customer number
Thanks in advance
You would need to describe exactly how you want the results to look for a
complete recommendation, but here is something from a previous post that could
be useful.
Name your list of Customers "Customers" and your list of Products "Products".
To create a list of Customers, enter the following array formula (commit with
CTRL+SHIFT+ENTER) in A2 of another worksheet and copy down until blanks are
returned:
=IF(ROWS($1:1)>SUM(1/COUNTIF(Customers,Customers)),"",
INDEX(Customers,MATCH(0,COUNTIF($A$1:A1,Customers),0)))
The first associated Product for the Customer is returned by entering the
following in B2 and copying down as needed:
=IF(A2="","",INDEX(Products,MATCH($A2,Customers,0)))
The rest of the associated codes are obtained by entering this array formula
(commit with CTRL+SHIFT+ENTER) in C2 and copying across and down as needed:
=IF(COLUMN(B$1)>COUNTIF(Customers,$A2),"",
INDEX(INDIRECT("'"&CELL("filename",Products)&
"'!R"&MATCH($A2&B2,Customers&Products,0)+ROW(Products)&
"C"&COLUMN(Products)&":R"&ROWS(Products)+ROW(Products)-1&
"C"&COLUMN(Products),FALSE),
MATCH($A2,INDIRECT("'"&CELL("filename",Customers)&
"'!R"&MATCH($A2&B2,Customers&Products,0)+ROW(Customers)&
"C"&COLUMN(Customers)&":R"&ROWS(Customers)+ROW(Customers)-1&
"C"&COLUMN(Customers),FALSE),0)))