Lookup 4 columns wherein 2 contain dates (my required date may fall b/w 2 lookup dates) and return value from the 4th column

[ca.moazzam]

Hi friends

I have data (and a lot of it) on a sheet in the format given below (Data area). I have another sheet wherein some records of main data have been copied (like Supplier Code which repeats in some records in the Result sheet) and Date (which falls between "From" and "To" dates of that particular supplier). There is only one combination of supplier code and the required date (I mean all records are unique). I want my resuts sheet to first match the required Supplier Code in the Data Sheet (which is simple), then lookup the record of that supplier to find the row in which my result date falls between From and To dates. I have mentioned my required values in red below for ease of understanding.

I know that if I had a numeric value in the Case column, I could have used SUMIFS or SUMPRODUCT functions. But what to do in this case? May be some combination of INDEX/MATCH could work. Can anybody help me on this?

Thanks

Moazzam
[TABLE="width: 501"]
<tbody>[TR]
[TD]Data
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Supplier code
[/TD]
[TD]From date
[/TD]
[TD]To date
[/TD]
[TD]Case
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]111
[/TD]
[TD]1-Jan-10
[/TD]
[TD]31-Dec-10
[/TD]
[TD]A
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]111
[/TD]
[TD]1-Jan-11
[/TD]
[TD]31-Dec-11
[/TD]
[TD]B
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]112
[/TD]
[TD]1-Jan-10
[/TD]
[TD]31-Dec-10
[/TD]
[TD]C
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]112
[/TD]
[TD]1-Jan-11
[/TD]
[TD]31-Dec-11
[/TD]
[TD]D
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]Result
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Supplier code
[/TD]
[TD]Date
[/TD]
[TD]Case
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]111
[/TD]
[TD]25-Jun-10
[/TD]
[TD]A (required)
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]112
[/TD]
[TD]1-Jul-11
[/TD]
[TD]D (required)
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Robert Mika]

Data

*	A	B	C	D
Data	*	*	*
Supplier code	From date	To date	Case
A
B
C
D

<tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: right"]111[/TD]
[TD="align: right"]01-Jan-10[/TD]
[TD="align: right"]31-Dec-10[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: right"]111[/TD]
[TD="align: right"]01-Jan-11[/TD]
[TD="align: right"]31-Dec-11[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: right"]112[/TD]
[TD="align: right"]01-Jan-10[/TD]
[TD="align: right"]31-Dec-10[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="align: right"]112[/TD]
[TD="align: right"]01-Jan-11[/TD]
[TD="align: right"]31-Dec-11[/TD]

</tbody>

Excel tables to the web >> Excel Jeanie HTML 4
result

*	A	B	C	D
Result	*	*	*
Supplier code	Date	Case	*
A	*
D	*

<tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: right"]111[/TD]
[TD="align: right"]25-Jun-10[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: right"]112[/TD]
[TD="align: right"]01-Jul-11[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Formula in C3:

=INDEX(Data!$D$3:$D$6,MATCH(1,INDEX((Data!$C$3:$C$6 >= B3)*(Data!$B$3:$B$6 <= B3)*(Data!$A$3:$A$6=A3),),0))

 

[ca.moazzam]

Great solution Robert. Bundle of thanks for your response within 15 minutes.

Problem solved. Thanks.

Moazzam