VBA Index Match with 2 criteria

[Revier]

Hi All

My brain is struggling to wrap itself around index and matching with 2 criteria where one criteria is exact match and the other is less than

My dataset (apologies its not a mini sheet, work computer restricting the download)

A​	B​	C​
800101	X1	24/07/2020
800102	X1	30/07/2020
800111	X1	05/08/2020
800101	X2	01/09/2020
800110	X1	05/05/2020
800106	X3	11/12/2020

What I want to achieve is to match column A to exactly "800101" then match Column C to nearest date to <Today and return the corresponding value from column B.

Iv tried using this formula, but it only returns the B column value that is closest to today and doesn't factor in the match for 800101 in column A

=INDEX(B2:B20, MATCH(TODAY(),C2:C20, 1),MATCH(800101,A2:A20,0))

I was then hoping to use VBA to replicate the index and match using worksheetfunctions

I would really appreciate Any advice you can give me.

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Revier]

Sorry, it excel 2016.

 

[Fluff]

Thanks for that, how about

+Fluff 1.xlsm
	A	B	C	D	E	F
1
2	800101	X1	24/07/2021		800101	X2
3	800102	X1	30/07/2020
4	800111	X1	05/08/2020
5	800101	X2	01/09/2020
6	800110	X1	05/05/2020
7	800106	X3	11/12/2020
8
Test

Cell Formulas
Range	Formula
F2	F2	=INDEX(B2:B20,MATCH(1,(A2:A20=E2)*(C2:C20=MAX(IF(A2:A20=E2,IF(C2:C20<TODAY(),C2:C20,0)))),0))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Revier]

Thats work perfect, thankyou so much fluff, ill try to pick apart the forumula to understand how works now, so i can help others in future.

Much Love <3

 

[Fluff]

You're welcome & thanks for the feedback.