CODEWITHSUNDEEP
excel
rauf741
rauf741

Reputation: 35

Excel Formula to Match Items Within a Date Range

I'm trying to match a product sale date with the range date of sales. I have the following so far: 

=INDEX(A3,MATCH(1,((H:H=A3)*(B:B>=I3)*(B:B<=J3)),0))

I'm trying to have the formula produce a result if column A matches column H and if column B is between column I & J, where J may not have occurred yet.

I have added a photo of my sample spreadsheet here

EDIT: column E15 isn't returning any results See Here

Upvotes: 0

Views: 394

Answers (2)

Scott Craner
Scott Craner

Reputation: 152505

Use this array formula:

=IF(SUM(($H$3:$H$12=$A3)*($I$3:$I$12<=B3)*(IF($J$3:$J$12<>"",$J$3:$J$12,TODAY()+1)>$B3)),$A$3,"")

Being an array formula it must be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode.

Upvotes: 1

GarrettS1
GarrettS1

Reputation: 66

Could you use something like this in the cell where you want the value to occur? 

=IF(A1=H1,IF(AND(I2<B2,B2<J2),TRUE,FALSE),FALSE)

You can then replace "True" and "False" with other statements to produce different outcomes.

Upvotes: 0

Related Questions