How to use INDEX in an ARRAY that changes depending on a criteria

[Daniel @ Logicim]

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

 

[Bernard Liengme]

Go to Help and learn about VLOOKUP then come back if you nee more help
best wishes

 

[Daniel @ Logicim]

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?

 

[S Davis]

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?

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)*DateSold)),"Not yet
purchased",=sum((ClientNames=$A2)*(ItemsSold=A$1)*DateSold))

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