Index Match returns a #ref error

[Bill416]

Below is a sample of my spreadsheet using an index match formula.

There are 2 tables on separate sheets in one spreadsheet.

Tabs are labelled Membership Trrips and Trip Data. the purpose is to combine 2 data sets from data collected on a website. The first table contains membership roster information. The second contains the data collected when members go out on trips. The tables shown below have been consolidated on the Members Trips sheet to determine if the error occurs when on separate sheets.

I currently manually export the data in an xlxs format and then save it in an xlsm format. I reformat the data to be in the same form for each sheet. For example, a date is formatted using the same date format. Numbers are set to no decimal points.

I am using the Member ID and the Date to return the contents of the Registered field in the Trip Datasheet. the 2 formulas that I have return #REF and #N/A. Can anyone help?

Bill

mnm-membership-Trips-master-^12023-24(AutoRecovered).xlsm
	A	B	C	D								L	M	N
3	Member ID	Last Name	First Name	User Role								Trip Date18	Trip Date2	Trip Date3
4												April 24, 2023	2023-05-01	2023-05-08
5				Week								8	9	10
6	3	Thompson	Brent (AM)
7	4	Klassen	Hank
8	9	Thompson	Lesley (AM)
9	12	Hamilton	Janet									#REF!
10	16	Wetter	Brian									#REF!
11	17	Howard	Erlinda									#N/A
12	18	Howard	Frank
13	19	Barnes	Penny (G)
14	20	Atkinson	David
Membership Trips

Cell Formulas
Range	Formula
L9	L9	=INDEX($B$301:$D4500,MATCH(1,($A9=$B$301:$B4500)*(L$4=$C$301:$C4500)),0,3)
L10	L10	=INDEX($B$301:$D4501,MATCH(1,(L$4=$C$301:$C4501)*($A10=$B$301:$B4501)),0,3)
L11	L11	=INDEX('Trip Data'!$B$2:$D4000,MATCH(1,('Trip Data'!$B$1:$B625=$A12)*('Trip Data'!$C4:$C625=L$4),0),3)

Named Ranges
Name	Refers To	Cells
'Trip Data'!_FilterDatabase	='Trip Data'!$B$1:$K$184	L11

mnm-membership-Trips-master-^12023-24(AutoRecovered).xlsm
	A	B	C	D
300	#records	Member ID	Trip Date2	Registered
301	40	4	April 24, 2023	1
307	500	4	June 19, 2023	1
308	532	4	June 26, 2023	1
309	599	4	July 3, 2023	1
310	34	12	April 24, 2023	1
317	469	12	June 19, 2023	1
318	543	12	June 26, 2023
319	602	12	July 3, 2023	1
320	24	16	April 24, 2023	1
321	250	16	May 22, 2023	1
322	58	17	April 24, 2023	1
323	399	17	June 12, 2023	1
324	56	18	April 24, 2023	1
325	398	18	June 12, 2023	1
Membership Trips

 

[Fluff]

The formula in L9 should be

=INDEX($B$301:$D4500,MATCH(1,($A9=$B$301:$B4500)*(L$4=$C$301:$C4500),0),3)

You had a closing bracket in the wrong place.

 

[Bill416]

It now returns #N/A in all cases.

 

[Fluff]

In that case use the Evaluate formula button on the formula tab to see whether the problem is with the dates or the ID.
I suspect that the "dates" in col C are not real dates.

 

[Bill416]

I checked both column C and Row 4 Trip Date and all are formatted the same.

I have attached images of the formatting and the Evaluate Formula and I think the ID is fine but there is still something wrong with the Date.

 

[Fluff]

Very difficult to tell from those images, but it looks as though the dates are not actual dates.
If you select C301:C4500 & change the format to General do you still see dates?

 

[Bill416]

When I changed the format to General for C301:C4500 the first row changed to a number the rest still show as dates. When I go to Format and check these, the sample shows a number the rest show dates.

 

[Fluff]

In that case they are not real dates, but text. You will need to convert them all to dates.

 

[Bill416]

Thanks you this worked.

 

[Fluff]

You're welcome & thanks for the feedback.