On Dec 17, 12:37 pm, Daniel @ Logicim
wrote:
I know about VLOOKUP and INDEX and MATCH and OFFSET.
I need use the function in a "volatile" ARRAY.
How do I create the ARRAY with a client lookup criteria?

"Bernard Liengme" wrote:
Go to Help and learn about VLOOKUP then come back if you nee more help
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"Daniel @ Logicim" <Daniel @ wrote in
...
Column A = Client names
Column B = Items sold
Column c = Date sold

I want to get the Item Date of Purchase for a client who is indicated on
another worksheet.
There can be many Items per client and many clients for same Items

Not the most fun option to setup, but it works.

Create a matrix on a seperate sheet - Client Names running along the
side, and Items along the top.
Next create named ranges (for simplicity) ie. A:A = "ClientNames".

Now in your matrix all you need to do is:
=sum((ClientNames=$A2)*(ItemsSold=A$1)*DateSold)

Enter with ctrl-shift-enter so you see curly braces { } around your
formula.

Drag to fill your matrix.

Add error checks as necessary:

=if(isna(sum((ClientNames=$A2)*(ItemsSold=A$1)*Dat eSold)),"Not yet
purchased",=sum((ClientNames=$A2)*(ItemsSold=A$1)* DateSold))

Voila. This will work best if you are polling client names from a
database.