Return a value in a row, based on two other rows

[hmsiegel]

I have a formula that I’ve used successfully in MS Office.

=IF($E5="","",(LOOKUP(2,1/(startYear<=$E5)/(endYear>=$E5),sinclairA)))

Where startYear, endYear, and sinclairA are named ranges on a separate sheet. This formula does not seem to work in Calc.
I am looking for the value in a row (sinclairA), if that year falls between two other years. For example, B2 is 2013 and C2 is 2016. If the year we are looking at is 2015, the formula should return the value in D2 (sinclairA).
Any idea how I can do this in Calc?

Thanks in advance

[SM_Riga]

Hello.

I am not strong in MS Office Excel syntax, but for Calc it looks like you will get Divide by 0 error in case your conditional tests return 0 (boolean False). In this case I would prefer not to use lookup functions (Lookup, Hlookup, Vlookup), cause they can produce unpredictable results, based on user settings, range sorting and use of Regular Expressions - [Tutorial] VLOOKUP questions and answers (View topic) • Apache OpenOffice Community Forum Lookup function needs Searchrange to be sorted in ascending order, numbers before text. Can be pretty messy.

My suggestion is based on Array IF formula.

In columns A, B and C there are three Named ranges startYear, endYear and sinclairA. F1 cell is year to test. So just enter an array formula in any cell you want (F2 in my case):

=TEXTJOIN("";1;IF(($F$1>=startYear)*($F$1<=endYear);sinclairA;""))

Replace semicolons with commas if needed - based on your LO settings. To enter an array formula, you need to press Ctrl + Shift + Enter.

[hmsiegel]

Thanks. That did the trick.

[jimk]

@hmsiegel: Please mark the answer correct, as explained in guidelines for asking.