INDEX and MATCH

[jislandhopper]

Afternoon Everyone,

I hope someone can assist with a small problem. I want to get a formula which searches 2 criteria and return a answer. It's like a Vlookup but with an additional criteria. I've tried consolidating the cells but someone told me that using INDEX and MATCH are a better was to do this. I've tried writing the formula but it doesn't seem to work, so I'm hoping the community can help me out.


I've created a simplified reference table and a small table as example as what I'm after in return. The reference table is where I pull the information from and I want the formula to review Column A and B from the reference data and return an answer on. Column D on the results table. I've added in the reference numbers which I added manually. Can anyone point me in the right direction?



[TABLE="width: 281"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD="colspan: 2"]Reference table[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Col A[/TD]
[TD]Col B[/TD]
[TD]Col C[/TD]
[/TR]
[TR]
[TD]Depo 1[/TD]
[TD="align: right"]06/08/2019[/TD]
[TD]REF 123[/TD]
[/TR]
[TR]
[TD]Depo 1[/TD]
[TD="align: right"]07/08/2019[/TD]
[TD]REF 124[/TD]
[/TR]
[TR]
[TD]Depo 1[/TD]
[TD="align: right"]08/08/2019[/TD]
[TD]REF 125[/TD]
[/TR]
[TR]
[TD]Depo 1[/TD]
[TD="align: right"]09/08/2019[/TD]
[TD]REF 126[/TD]
[/TR]
[TR]
[TD]Depo 1[/TD]
[TD="align: right"]10/08/2019[/TD]
[TD]REF 127[/TD]
[/TR]
[TR]
[TD]Depo 1[/TD]
[TD="align: right"]11/08/2019[/TD]
[TD]REF 128[/TD]
[/TR]
[TR]
[TD]Depo 1[/TD]
[TD="align: right"]12/08/2019[/TD]
[TD]REF 129[/TD]
[/TR]
[TR]
[TD]Depo 1[/TD]
[TD="align: right"]13/08/2019[/TD]
[TD]REF 130[/TD]
[/TR]
[TR]
[TD]Depo 1[/TD]
[TD="align: right"]14/08/2019[/TD]
[TD]REF 131[/TD]
[/TR]
[TR]
[TD]Depo 2[/TD]
[TD="align: right"]15/08/2019[/TD]
[TD]REF 132[/TD]
[/TR]
[TR]
[TD]Depo 2[/TD]
[TD="align: right"]16/08/2019[/TD]
[TD]REF 133[/TD]
[/TR]
[TR]
[TD]Depo 2[/TD]
[TD="align: right"]17/08/2019[/TD]
[TD]REF 134[/TD]
[/TR]
[TR]
[TD]Depo 2[/TD]
[TD="align: right"]18/08/2019[/TD]
[TD]REF 135[/TD]
[/TR]
[TR]
[TD]Depo 2[/TD]
[TD="align: right"]19/08/2019[/TD]
[TD]REF 136[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Results Table[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Col A[/TD]
[TD="align: center"]Col B[/TD]
[TD="align: right"]Col D[/TD]
[/TR]
[TR]
[TD]DEPO [/TD]
[TD="align: center"]Date from [/TD]
[TD="align: right"]Ref number[/TD]
[/TR]
[TR]
[TD]Depo 1[/TD]
[TD="align: center"]13/08/2019[/TD]
[TD="align: right"] REF 130[/TD]
[/TR]
[TR]
[TD]Depo 1[/TD]
[TD="align: center"]08/08/2019[/TD]
[TD="align: right"] REF 125[/TD]
[/TR]
[TR]
[TD]Depo 2[/TD]
[TD="align: right"]22/08/2019 [/TD]
[TD="align: right"]REF 139[/TD]
[/TR]
[TR]
[TD]Depo 2[/TD]
[TD="align: center"]19/08/2019 [/TD]
[TD="align: right"]REF 136[/TD]
[/TR]
</tbody>[/TABLE]


Thanks,
Jason

 

[Fluff]

How about

Book1
	A	B	C	D	E	F	G	H	I
1	Col A	Col B	Col C				DEPO	Date from	Ref number
2	Depo 1	06/08/2019	REF 123				Depo 1	13/08/2019	REF 130
3	Depo 1	07/08/2019	REF 124				Depo 1	08/08/2019	REF 125
4	Depo 1	08/08/2019	REF 125				Depo 2	22/08/2019	#N/A
5	Depo 1	09/08/2019	REF 126				Depo 2	19/08/2019	REF 136
6	Depo 1	10/08/2019	REF 127
7	Depo 1	11/08/2019	REF 128
8	Depo 1	12/08/2019	REF 129
9	Depo 1	13/08/2019	REF 130
10	Depo 1	14/08/2019	REF 131
11	Depo 2	15/08/2019	REF 132
12	Depo 2	16/08/2019	REF 133
13	Depo 2	17/08/2019	REF 134
14	Depo 2	18/08/2019	REF 135
15	Depo 2	19/08/2019	REF 136
Sheet2S

Cell Formulas
Range	Formula
I2	=INDEX($C$2:$C$15,MATCH(G2&"|"&H2,INDEX($A$2:$A$15&"|"&$B$2:$B$15,0),0))