CODEWITHSUNDEEP
excelexcel-formula
Mischa Urlings
Mischa Urlings

Reputation: 89

Excel formula retrieve value if between two dates

I made the following formula to retrieve a certain value if a date is between or equal as other dates, and the name of the equipment should be the same:

 =IF(AND([@Datum] >='Orderregistratie (version 1).xlsm'!Tabel1510[@[Datum uitvoering]],[@Datum] <= 'Orderregistratie (version 1).xlsm'!Tabel1510[@[Datum eind uitvoering]],'Grafieken Availability'!$B$4='Orderregistratie (version 1).xlsm'!Tabel1510[@Equipment]),'Orderregistratie (version 1).xlsm'!Tabel1510[@Voorbereidingsproces],"")

The answer to the formula should give a lot of numbers but almost every cell in the column retrieves "". That means the formula doesn't find a value or the and function isn't working. Does anybody know how to adapt my formula so it will work?

After some time I came to the following array formula:

{=INDEX('Orderregistratie (version 1).xlsm'!Tabel1510[Voorbereidingsproces],MATCH(1,ALS([@Datum]>='Orderregistratie (version 1).xlsm'!Tabel1510[Melding],ALS([@Datum]<='Orderregistratie (version 1).xlsm'!Tabel1510[Datum eind uitvoering],1)),0))}

Is it possible to adapt this formula so the name of the equipment must be the same?

Upvotes: 0

Views: 52

Answers (1)

Mischa Urlings
Mischa Urlings

Reputation: 89

=ALS.FOUT(INDEX('Orderregistratie (version 1).xlsm'!Tabel1510[Voorbereidingsproces];VERGELIJKEN(1;([@Datum] >='Orderregistratie (version 1).xlsm'!Tabel1510[Melding])*([@Datum] <= 'Orderregistratie (version 1).xlsm'!Tabel1510[Datum eind uitvoering])*([@Equipment]='Orderregistratie (version 1).xlsm'!Tabel1510[Equipment]);0));"")

Above is the code that i made to make it work! Found it myself

Upvotes: 1

Related Questions