Lookup Values with multiple criteria

Xlitup

New Member
Joined
Jan 16, 2018
Messages
22
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.
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,192
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))
 
Last edited:

Xlitup

New Member
Joined
Jan 16, 2018
Messages
22
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,192
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.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,109,020
Messages
5,526,293
Members
409,694
Latest member
bastos21

This Week's Hot Topics

Top