Ask Your Question
0

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

asked 2018-06-03 03:51:06 +0100

hmsiegel gravatar image

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

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
2

answered 2018-06-03 18:36:25 +0100

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 - https://forum.openoffice.org/en/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.

IF_array_screenshot

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.

edit flag offensive delete link more

Comments

Thanks. That did the trick.

hmsiegel gravatar imagehmsiegel ( 2018-06-04 11:21:04 +0100 )edit
1

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

Jim K gravatar imageJim K ( 2018-06-04 20:52:12 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2018-06-03 03:51:06 +0100

Seen: 41 times

Last updated: Jun 03 '18