Complex lookup returning result of a formula

orehovka

New Member
Joined
Jan 31, 2014
Messages
7
Hello all!

I have two worksheets with partially overlapping data and I need to get a number of dates, that fall between two dates when certain criteria is met:
Sheet 1:
1_zpse8b9534c.jpg


Sheet 2:
2_zps6a8c9f2d.jpg


What I'm trying to do is:
- match a combination of cells in the first sheet, for example Red&Tree, with the same combination in the second sheet (not visible in the example, but available in the real document)
- then check what number of dates on the same row in Sheet 2 (from columns C:G) fall between Date 1 and Date 2 in Sheet 1.
I'm able to cover separately the matching of cells and number of dates calculation, but I can't combine these two criteria together.
Do you have any ideas how to solve this?
 

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,201
Sheet1

E1: Idx
E2, control+shift+enter, not just enter, and copy down:
Rich (BB code):
=MATCH($A2,IF(Sheet2!$B$2:$B$6=$B2,Sheet2!$A$2:$A$6),0)
F1: Count
F2, control+shift+enter, not just enter, and copy down:
Rich (BB code):
=SUM(IF(INDEX(Sheet2!$C$2:$G$6,$E2,0)>=INT($B2),
  IF(INDEX(Sheet2!$C$2:$G$6,$E2,0)<=INT($C2),1)))

Is this what you are after?
 

orehovka

New Member
Joined
Jan 31, 2014
Messages
7
Not exactly like this, but thank you for your suggestion, Aladin. I was trying to put these two operations in one formula.
I added another concatenated column in Sheet 2, so that I have already linked all combinations that generate dates to check. Now I'm able to match colors+type from Sheet 1 to Sheet 2 with vlookup or Match, but I still can't make it diplay my second formula and not the value of a cell.
For the comparison between dates I made up the following:
=SUMPRODUCT((Sheet2!$C$2:$F$2>B2)*(Sheet2!$C$2:$F$2)<c2)) but="" i="" want="" this="" check="" to="" be="" activated="" only="" when="" specific="" combination="" green+rock="" occurs="" in="" sheet="" 1.<="" html=""></c2))>
 
Last edited:

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
Not exactly like this, but thank you for your suggestion, Aladin. I was trying to put these two operations in one formula.
I added another concatenated column in Sheet 2, so that I have already linked all combinations that generate dates to check. Now I'm able to match colors+type from Sheet 1 to Sheet 2 with vlookup or Match, but I still can't make it diplay my second formula and not the value of a cell.
For the comparison between dates I made up the following:
=SUMPRODUCT((Sheet2!$C$2:$F$2>B2)*(Sheet2!$C$2:$F$2)<c2)) but="" i="" want="" this="" check="" to="" be="" activated="" only="" when="" specific="" combination="" green+rock="" occurs="" in="" sheet="" 1.<="" html=""></c2))>

Try to post the original exhibits in a form that is directly readable into Excel along with the desired results.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,893
Messages
5,598,712
Members
414,254
Latest member
MarieCo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top