multiple criteria match

[maxvo]

hi,

I am trying to do an index match for 2 criteria. Lets say i have to return a random number from column C. Matching criteria 1 is DEF and matching criteria 2 is 01/11/2021 (I'd like to match the closest date). So ideally I would return Random(6).

A B C
ABC 01/01/2020 random(1)
ABC 01/01/2021 random(2)
ABC 01/01/2022 random(3)
DEF 01/01/2020 random(4)
DEF 01/01/2021 random(5)
DEF 01/01/2022 random(6)

I'm struggling to merge the 2 criteria into a single formula. I thought along the lines of sumproduct or Match(criteria1&2,range1&2) but struggling to formulate this.

Help would incredibly appreciated.

 

[Nishant94]

Unknown
	A	B	C
1	Name	Date	Random
2	ABC	01/01/2020	random(1)
3	ABC	01/01/2021	random(2)
4	ABC	01/01/2022	random(3)
5	DEF	01/01/2020	random(4)
6	DEF	01/01/2021	random(5)
7	DEF	01/01/2022	random(6)
8
9	Matching Criteria 1	Matching Criteria 2	Result
10	DEF	01/11/2021	random(6)
Sheet6

Cell Formulas
Range	Formula
C10	{=INDEX($C$2:$C$7,MATCH(MIN(IF(A10=$A$2:$A$7,ABS($B$2:$B$7-B10))),IF(A10=$A$2:$A$7,ABS($B$2:$B$7-B10)),0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[maxvo]

i could not be more grateful