Index and Match Multiple Critera

[Sisk]

Hi,

I am trying to compare last months cost ledger with this months. Last month I went through some 7000 line items and assigned them a cost heading manually as the accounts system does not do it for me. I don't want to revisit another 7000 line items in the new ledger rather the new ledger items which have landed in the month.

Unfortunately I don't have any unique transaction ID's so I'm having to run an index / match formula based on an internal transaction reference and a description (2 criteria).

However, I've only got as far as running the formula based on one criteria and would like to extend this to two (or even three) to ensure as many entries are captured and help in minimising coding of old items.

Where Column K (Internal Ref for this month) and Column L (External Ref this month) and Columns X to Z contain the Internal Ref, External Ref and Cost Head to Last Month:

T8= =INDEX(X8:Z7213,MATCH(K8,X8:X7213,0),3)

Can anyone kindly point me in the right direction?

 

[Sisk]

That should read =INDEX(W8:Y7213,MATCH(K8,W8:W7213,0),3)

 

[Fluff]

How about
=INDEX($Z$8:$Z$7213,MATCH(K8&"|"&L8,$X$8:$X$7213&"|"&$Y$8:$Y$7213,0))

Although from the image it looks as though the lookup range is W:Y not X:Z

 

[Sisk]

Hi fluff,

No joy with that. #VALUE I've also tried

=INDEX($W$8:$Y$7212,MATCH(K8&"|"&L8,$W$8:$W$7213&"|"&$X$8:$X$7213,0))

What are the "|" for?

 

[Fluff]

It should be
=INDEX($Y$8:$Y$7212,MATCH(K8&"|"&L8,$W$8:$W$7213&"|"&$X$8:$X$7213,0))

 

[Sisk]

Hi fluff,

Thanks - tried that again but to no avail. Last months data is in columns W:Y (including cost heading) and this months cost ledger is in Columns A:S in Column T I'm trying to allocate a cost head to each line item based on the internal and external ref in Column K and Column L. Hope I've explained that properly.

 

[Fluff]

Can you post some sample data using the XL2BB add-in?

 

[Sisk]

export51765.xml
	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
7	Tran Date	Internal Ref	External Ref	PO Number	Quantity	Unit	Actual Costs	Accruals	Total (STG)	Month End				Internal Ref	External Ref	Cost Head
8	03/04/2018	00000001	Expenses: Jez		0		461.00	-	461.00	30/04/2018	#VALUE!			00000001	Expenses: Jez	Staff
9	19/06/2018	00000003	Expenses: Michael B		0		4,960.00	-	4,960.00	30/06/2018	#VALUE!			00000003	Expenses: Michael B	Staff
10	26/06/2018	00000004	Expenses: Michael B		0		1,742.07	-	1,742.07	30/06/2018				00000004	Expenses: Michael B	Staff
11	30/06/2018	24892	John Expenses		0		- 1,742.07	-	- 1,742.07	30/06/2018				24892	John Expenses	Overhead
12	24/07/2018	00000012	Expenses: Ian J		0		530.00	-	530.00	31/07/2018				00000012	Expenses: Ian J	Materials
13	08/08/2018	00000044	Expenses: John H		0		39.84	-	39.84	31/08/2018				00000044	Expenses: John H	Plant
14	21/08/2018	00000014	Expenses: Ian J		0		530.00	-	530.00	31/08/2018				00000014	Expenses: Ian J	Subcontract
15	22/08/2018	00000045	Expenses: John Hog		0		30.00	-	30.00	31/08/2018				00000045	Expenses: John Hog	Staff
16	22/08/2018	00000045	Expenses: John Hog		0		13.43	-	13.43	31/08/2018				00000045	Expenses: John Hog	Staff
17	31/10/2018	00000050	Expenses: John Hog		0		106.74	-	106.74	31/10/2018				00000050	Expenses: John Hog	Staff
18	31/10/2018	00000050	Expenses: John Hog		0		93.56	-	93.56	31/10/2018				00000050	Expenses: John Hog	Staff
19	31/10/2018	00000005	Expenses: Michael B		0		84.00	-	84.00	31/10/2018				00000005	Expenses: Michael B	Staff
20	31/10/2018	00000008	Expenses: Sandy M		0		236.90	-	236.90	31/10/2018				00000008	Expenses: Sandy M	Staff
21	31/10/2018	00000003	Expenses: Jack A		0		112.18	-	112.18	31/10/2018				00000003	Expenses: Jack A	Staff
Data

Cell Formulas
Range	Formula
T8	T8	=INDEX($Y$8:$Y$7212,MATCH(K8&"|"&L8,$W$8:$W$7213&"|"&$X$8:$X$7213,0))
T9	T9	=INDEX($Y$8:$Y$7212,MATCH(K9&""&L9,$W$8:$W$7213&""&$X$8:$X$7213,0))
S8:S21	S8	=EOMONTH(J8,0)

 

[Fluff]

Do you have the dynamic array functions like Filter, Sequence?

 

[Sisk]

Do you have the dynamic array functions like Filter, Sequence?

Not sure - can't see any. FWIW I've got Microsoft Office 365 ProPlus if that's any help?