Index Match help please to find a price between multiple dates

[Jeffrey Green]

=INDEX(Price,MATCH(1,((Part=J2)*(BeginDate>=Import)*(EndDate<=Import)),0))

I always used Named Ranges
PRICE is the price of the PARTI am trying to find. The PART Column is not unique. it has price changes that i need to manage
PART is the part number i am looking up the PRICE for
J2 is the cell with the Part Number being imported
BEGINDATE is when the PRICE changed
IMPORT is the DATE the part was imported into the US, and i need the price for that part on this date
ENDDATE is the last day of the previous price, and a new price is in the table

thanks . . . I get #N/A for all rows.

 

[StephenCrump]

What happens if you make the formula?

=INDEX(Price,MATCH(1,((Part=J2)*(BeginDate<=Import)*(EndDate>=Import)),0))

... and what about the current prices? If the EndDate is blank, you'll need to adjust the formula to allow for this.

 

[Jeffrey Green]

Still get NA . . . .thanks. Someone thinks this needs to be an array formula. . . . it is just a regular formula.

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Someone thinks this needs to be an array formula

It is & depending on your xl version may need to be entered with Ctrl Shift Enter.