# Lookup Values with multiple criteria

#### Xlitup

##### New Member
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.

### Excel Facts

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

##### MrExcel MVP
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

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]

##### MrExcel MVP

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.

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:

Replies
23
Views
959
Replies
2
Views
61
Replies
11
Views
74
Replies
0
Views
121
Replies
13
Views
539