INDEX AND MATCH #REF ERROR

[Mina Persona]

Hello,

I am having error on some cells only when i try to use the below function and please see the below image:
=INDEX($A$2:$A$13,MATCH(I3,$B$2:$B$13,0),MATCH(1,IF(J3>=$C$2:$C$13,IF(J3<=$D$2:$D$13,1)),0))

 

[Peter_SSs]

Welcome to the MrExcel board!

For the future: You will get faster and better responses if you can give us sample data in a form we can copy & test with.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Try this formula in cell H2 and copy down

=FILTER(A$2:A$13,(B$2:B$13=I2)*(C$2:C$13<=J2)*(D$2:D$13>=J2),"")

 

[Mina Persona]

Hi,

Thank you for the guidance and support, the filter function is not available in my ms office could you please help with another alternative.

 

[Mina Persona]

Welcome to the MrExcel board!

For the future: You will get faster and better responses if you can give us sample data in a form we can copy & test with.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Try this formula in cell H2 and copy down

=FILTER(A$2:A$13,(B$2:B$13=I2)*(C$2:C$13<=J2)*(D$2:D$13>=J2),"")

Book10.xlsx
	A	B	C	D	E	F	G	H	I	J
1	POLICY NO	CLIENT NAME	START DATE	END DATE				POLICY NO	CLIENT NAME	CLAIM DATE
2	AMI/CMED/00076/2023/1	Client 1	01-Jul-23	30-Jun-24				AMI/CMED/00059/2022/1	Client 2	4-Jul-23
3	AMI/CMED/00059/2022/1	Client 2	01-Oct-22	30-Sep-23				#REF!	Client 2	26-Nov-22
4	AMI/CMED/00059/2022/2	Client 2	01-Oct-23	30-Sep-24				#REF!	Client 2	20-Feb-23
5	AMI/CMED/00030/2021/1	Client 3	01-Aug-21	31-Jul-22				#REF!	Client 2	21-Feb-23
6	AMI/CMED/00030/2021/2	Client 3	01-Aug-22	31-Jul-23				#REF!	Client 2	1-Apr-23
7	AMI/CMED/00030/2021/3	Client 3	01-Aug-23	31-Jul-24				AMI/CMED/00059/2022/1	Client 2	3-Sep-23
8								AMI/CMED/00076/2023/1	Client 1	18-Oct-23
9								AMI/CMED/00076/2023/1	Client 1	18-Oct-23
10								#REF!	Client 3	3-Jul-22
11								#REF!	Client 3	19-Jan-22
12								#REF!	Client 3	14-Apr-22
13								#REF!	Client 3	25-Apr-22
Sheet1

Cell Formulas
Range	Formula
H2:H13	H2	=INDEX($A$2:$A$13,MATCH(I2,$B$2:$B$13,0),MATCH(1,IF(J2>=$C$2:$C$13,IF(J2<=$D$2:$D$13,1)),0))
Press CTRL+SHIFT+ENTER to enter array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A:A	Cell Value	duplicates	text	NO

 

[awoohaw]

try this, you may need to enter the formula with the CNTL-SHFT-ENTR (CSE) Keystroke:

=INDEX($A$2:$A$13,
SUM(($C$2:$C$13<=J2)*($D$2:$D$13>=J2)*(I2=$B$2:$B$13)*(ROW($B$2:$G$13)-ROW($B$2)+1)))

 

[Mina Persona]

try this, you may need to enter the formula with the CNTL-SHFT-ENTR (CSE) Keystroke:

=INDEX($A$2:$A$13,
SUM(($C$2:$C$13<=J2)*($D$2:$D$13>=J2)*(I2=$B$2:$B$13)*(ROW($B$2:$G$13)-ROW($B$2)+1)))

Hello,

Thanks for the contribution but i think this just work on the first argument ($C$3:$C$8=I2), i think it is not checking if the date is between two dates.

 

[RoryA]

Try:

=IFERROR(LOOKUP(2,1/($B$2:$B$7=I2)/(J2>=$C$2:$C$7)/(J2<=$D$2:$D$7),$A$2:$A$7),"No policy match")

 

[Mina Persona]

Try:

=IFERROR(LOOKUP(2,1/($B$2:$B$7=I2)/(J2>=$C$2:$C$7)/(J2<=$D$2:$D$7),$A$2:$A$7),"No policy match")

Thank you very much really appreciated.

 

[RoryA]

Glad we could help.

 

[awoohaw]

Did you test it out? I find it does. The formula I suggested returns the same results as RoryA's. It just isn't an array formula.
But you have an answer which was you came for. Best Wishes.