hello @all,

i’m working in a quite complex sheet from which i have a reduced version here: vlookup_array_wish.ods

one task is to calculate start and end date of a job in a week, which is represented by entries in one row of the area J12:AM21, entries in one cell-triple like AC13:AE13 represent the work of one day,

my wish is an easy and ‘editable’ solution for the following task:

find the 1 in col ‘J’, in that row find the first day/date with either a number or ‘FV’ and put it in B7, find the last day/date with either a number or ‘FV’ and put it into H7, other text should be ignored,

as of now i use an endless long formula with nested if’s and vlookups in the cells B7 and H7, ‘it works’ … for numbers,

but it fails when the work contains special info like in AC15:AE15,

(workflow: with the ‘1’ in J12:J21 i select a job, vlookups in nested ifs directed by that ‘1’ work through the entries from left to right for the start date, and from right to left for the end date),

as working with ‘SUM’ special info like ‘FV’ blocks the formula, i can change this with more nested ifs, but … that’s not elegant …

i’d like to have a shorter / easier solution which is more handy for edits (like now adding ‘FV’),

i’m curious if vlookup can give back a range/vector/array … something i can work on with sumproduct or similar to get rid of the triple question for each day …

anybody a good idea?

imho VLookup is not adding correctly? deals with a similar request, but regarding the complexity of that thread i’m off from digging into it …

(any other enhancement welcome …)