Lookup Values with multiple criteria

[Xlitup]

Hi,

I am trying to lookup values from another sheet using the following formula:

=INDEX('Sheet2'!C1:C30,MATCH(1,IF('Sheet2'!D1:D30='Sheet1'!B1,IF('Sheet2'!E1:E30>'Sheet1'!F1,IF('Sheet2'!H1:H30>'Sheet1'!G1,1))),0))

It works fine but I would like to have the H1:H30 column of sheet 2 between Sheet1 G1 and Sheet1 G1+6. So more than sheet1 G1 date but less than Sheet1 G1+6months.

It is a little confusing but any help would be great.
Let me know if further clarification is needed.

Cheers.

 

[Aladin Akyurek]

In Sheet1, Control+shift+enter, not just enter:

=INDEX('Sheet2'!$C$1:$C$30,MATCH(1,IF('Sheet2'!$D$1:$D$30=$B1,IF('Sheet2'!$E$1:$E$30>$F1,IF('Sheet2'!$H$1:$H$30>$G1,IF(

'Sheet2'!$H$1:$H$30 < EDATE($G1,12*6),1)))),0))​

​

 

[Xlitup]

Thanks Aladin,

Yes you are absolutely right Ctrl+Shift+Enter with the formula.

However the formula did not work for me but the following Date formula did work. I don't knoe why did EDate give me different values but the following works just fine.

Thanks for the help.

=INDEX('Sheet2'!$C$1:$C$30,MATCH(1,IF('Sheet2'!$D$1:$D$30=$B1,IF('Sheet2'!$E$1:$E$30>$F1,IF('Sheet2'!$H$1:$H$30>$G1,IF(

'Sheet2'!$H$1:$H$30 < DATE(Year($G1),Month($G1)+6,Day($G1)),1)))),0))​

​

[/QUOTE]

 

[Aladin Akyurek]

Thanks Aladin,

Yes you are absolutely right Ctrl+Shift+Enter with the formula.

However the formula did not work for me but the following Date formula did work. I don't knoe why did EDate give me different values but the following works just fine.

Thanks for the help.

=INDEX('Sheet2'!$C$1:$C$30,MATCH(1,IF('Sheet2'!$D$1:$D$30=$B1,IF('Sheet2'!$E$1:$E$30>$F1,IF('Sheet2'!$H$1:$H$30>$G1,IF(

'Sheet2'!$H$1:$H$30 < DATE(Year($G1),Month($G1)+6,Day($G1)),1)))),0))​

​

You are welcome.

My bad.

EDATE($G1,6) should give you a 6 months later date.

This is equivalent to:

DATE(YEAR($G1),MONTH($G1)+6,DAY($G1))

but cheaper.