Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to use INDEX in an ARRAY that changes depending on a criteria
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to use INDEX in an ARRAY that changes depending on a criteria
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 message ... 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to use INDEX in an ARRAY that changes depending on a crite
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 message ... 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to use INDEX in an ARRAY that changes depending on a crite
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
lookup with INDEX MATCH formule depending on 2 conditions | Excel Worksheet Functions | |||
Populate, Depending on Criteria | Excel Discussion (Misc queries) | |||
vary the vlookup array depending on the value in a cell | Excel Worksheet Functions | |||
Delete row depending on criteria | Excel Discussion (Misc queries) | |||
Adding sales depending on 2 Criteria | Excel Worksheet Functions |