Index Match with multiple conditions including dates

[Sparkee]

Hi, I am new to Index Match formulas and I have a spreadsheet with 2 tabs. One tab has positions against a row of names, and the other has the names against rows of positions. I am trying to index match into the names against rows of positions conditional on the start and end dates. The index match I have is:

=INDEX(LinebyName!B2:B11, MATCH(1,((LinebyName!J2:J11=LinebyPosition!A2)*(LinebyName!G2:G12>=LinebyPosition!B1)*(LinebyName!H2:H11<=LinebyPosition!C1)),0))

Position against Names

Allocation sample file (1).xlsm
	I
6	Intern Line 5
LinebyName

Names against positions

Allocation sample file (1).xlsm
	B
2	#N/A
LinebyPosition

Cell Formulas
Range	Formula
B2	B2	=INDEX(LinebyName!B2:B11, MATCH(1,((LinebyName!J2:J11=LinebyPosition!A2)*(LinebyName!G2:G12>=LinebyPosition!B1)*(LinebyName!H2:H11<=LinebyPosition!C1)),0))
Press CTRL+SHIFT+ENTER to enter array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:F12	Cell Value	beginning with " "	text	NO

I would really appreciate any assistance.

Thanks
Michelle

 

[StephenCrump]

Based on only a very quick look ....

You have a reference to G2:G12, which looks it needs to be G2:G11?

By the way, with Excel 365, you have the XLOOKUP function available. You might want to give that a try?