Index Match Help!!!!!!!

[Jarlath]

Hi,

I have an issue trying to lookup a text cell when certain criteria are matched.

On sheet one I have the Order Date (Column C) and Fleet Number (H) columns

On sheet two I have contract start date (P), end date(V), fleet number(I) and maintenance status(Z).

I need a formula that will match the fleet numbers between sheet one and two & identify that the order date in sheet one falls between the start and end dates in sheet two. When both of these criteria have been met then return the maintenance status(text).

Any help would be greatly appreciated.

 

[gaz_chops]

Try

=index(sheet2!z1:z100,match(H1,if(and(c1>=p1100,c1<=v1:v100),i1:i100),0))

entered as an array

 

[Special-K99]

Try

=INDEX(Sheet2!Z1:Z1000,MATCH(Sheet1!H1,IF(Sheet1!C1>=Sheet2!P1:P1000,IF(Sheet1!C1<=Sheet2!V1:V1000,Sheet2!I1:Z1000)),0),1)
Array formula, use Ctrl-Shift-Enter

@gaz_chops: shouldn't your P V and I references read Sheet2!P1:100, Sheet2!V1:V100, Sheet2!I1:I100 ?

 

[gaz_chops]

Try

=INDEX(Sheet2!Z1:Z1000,MATCH(Sheet1!H1,IF(Sheet1!C1>=Sheet2!P1:P1000,IF(Sheet1!C1<=Sheet2!V1:V1000,Sheet2!I1:Z1000)),0),1)
Array formula, use Ctrl-Shift-Enter

@gaz_chops: shouldn't your P V and I references read Sheet2!P1:100, Sheet2!V1:V100, Sheet2!I1:I100 ?

Thanks, I realised after you posted but couldn't edit.

 

[Jarlath]

Thanks guys, worked perfectly.

 

[gaz_chops]

You're welcome.