Pull the data from a table based on two criteria

Hi,

I have a table with four columns- Model, Contract, Date range from and Date range to. Now I want to search a model of a particular date in this table to know what contract it had then. Attached a sample worksheet with data table, search items and expected result.

It would be great If some one can help me out

Thank you,

Vinay

Try

Formula:

Please Login or Register  to view this content.

Array Entered Formula

Enter with Ctrl+Shift+Enter

Try

=INDEX($D$3:$D$14,MATCH(1,(M4>$E$3:$E$14)*(M4<$F$3:$F$14)*($C$3:$C$14=M3),0))

Enter with Ctrl+Shift+Enter

Originally Posted by vinayanep

Hi Chullan and John ,

Thank you for the formula... It seems to be working well except in case When the search date fall on the day mentioned as "From" or "two". Ideally date range includes the day of "From" or "to".

Could you please spend a little more time on this to fine tune

Vinay

Modifying Johntopley's formula:

Formula:

Please Login or Register  to view this content.

Thank you very much Chullan and John
It is working fine with my table

Originally Posted by vinayanep

Thank you very much Chullan and John
It is working fine with my table

Glad to have helped.

If your problem has been solved,please make sure to Add Reputation ( Click the Small star at the bottom left of a members post) for all the members who have helped reach your solution.
Thanks!!