Indexing and matching specific terms

[chrisjako]

Can anyone help me with this forumal please,

I'm trying to import data from another CSV file named "merged_assessment.csv"

I have this working so far

=INDEX([merged_assessment.csv]merged_assessment!$E:$E,MATCH(B2,[merged_assessment.csv]merged_assessment!$L:$L,0))

But i only want it to return the merged_assesment.csv L column value if also these conditions are also met

1) the same row in merged_assessment.csv Column H, is equal the text "Autumn2"
and
2) the same row in merged_assessment.csv Column F, equals the text "2022-2023"

 

[Legacy 143009]

=INDEX([merged_assessment.csv]merged_assessment!$L:$L,MATCH(TRUE,([merged_assessment.csv]merged_assessment!$H:$H="Autumn2")*([merged_assessment.csv]merged_assessment!$F:$F="2022-2023"),0))

This is an array formula. You must press Ctrl+Shift+Enter together after paste.

 

[chrisjako]

=INDEX([merged_assessment.csv]merged_assessment!$L:$L,MATCH(TRUE,([merged_assessment.csv]merged_assessment!$H:$H="Autumn2")*([merged_assessment.csv]merged_assessment!$F:$F="2022-2023"),0))

This is an array formula. You must press Ctrl+Shift+Enter together after paste.

I'm just getting #N/A as a result
Is the column E$:E$ getting matched with the same value that's in B2?

 

[Legacy 143009]

Try like this:

=INDEX([merged_assessment.csv]merged_assessment!$L:$L,MATCH(1,([merged_assessment.csv]merged_assessment!$H:$H="Autumn2")*([merged_assessment.csv]merged_assessment!$F:$F="2022-2023")*([merged_assessment.csv]merged_assessment!$H:$H="Autumn2")([merged_assessment.csv]merged_assessment!$E:$E=B2),0))