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

  • Thread starter Thread starter Daniel @ Logicim
  • Start date Start date
D

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
 
Go to Help and learn about VLOOKUP then come back if you nee more help
best wishes
 
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?
 
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?
Click to expand...

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.
 
Back
Top