difficulty with match and index function with multiple results

dhamalian

New Member
Joined
Jun 17, 2013
Messages
9
Hello, I'm wondering if you can help me.
I'm a farmer working on a planting schedule.
I have a bunch of harvest dates next to a bunch of vegetables on a sheet named "Harvest Sheet"

Column A: Vegetable Name
Column B: Start Harvest Date
Column C: Last Harvest Date

On another sheet, entitled "CSA Plan Sheet" I have
Column A: Vegetable Name
Column B: Unit of Vegetable
Cell E2: date of Vegetable pickup

I would like to set up a system on "CSA Plan Sheet" where the number "1" is returned on column B if and only if the date of the vegetable pickup (E2 on CSA Plan Sheet) is in between (or equal to) the Start Harvest Date (Column B) and the Last Harvest Date (Column C) of the corresponding vegetable on the "Harvest Sheet". I can't get Index Match or VLookup to work, maybe because there are multiple vegetables with the same name under different dates.

For instance, on "Harvest Sheet" it'll be like
Vegetable Start Harvest End Harvest
Arugula 5/20 6/1
Arugula 7/20 8/1

I need the formula to look up all of the vegetables in the same name on the Harvest Sheet, and if just one matches the criteria of the "Vegetable pickup" date being inbetween the STart Harvest and End Harvest dates, to return a "1". I hope someone can help me!
Thank you
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Not exactly clear what you want to happen when you have the same date falling within multiple matching date ranges, but you could start with this. Make sure you modify the range size to yours.

Code:
=SUMPRODUCT((A2='Harvest Sheet'!$A$2:$A$[COLOR=#ff0000]3[/COLOR])*(E2>='Harvest Sheet'!$B$2:$B$[COLOR=#ff0000]3[/COLOR])*(E2<='Harvest Sheet'!$C$2:$C$[COLOR=#ff0000]3[/COLOR]))
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,239
Members
448,555
Latest member
RobertJones1986

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
Back
Top