Can vlookup return an array? easy way to find one value depending on multiple other entries

[newbie-02]

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 …)

[dscheikey]

Hi newbie-02,

i have improved the very smart function of @JonSUN a little bit, fixed an error there and created the second formula for the start of the job.
For this I edited your document directly and put it back here.

can vlookup return an array.ods

1.) JohnSUN had not considered your requirement that “FV” or numbers represent the beginning. This is now corrected.

2.) So that the formula of JonSUN works without errors, I have stored the date from M10 in line 10 also in the not visible cells, e.g. N10 or O10. This is the only way the formula works if the entry is not in the first of the three date columns.

3.) The formula for the start of the job looks a bit different. Here I wanted to use the LOOKUP function instead of Max, similar to the end. But that did not work. Now it is a bit more complicated with MIN and MATCH.

If you have any questions, please contact me via comment.

To answer your original question: No sorry VLOOKUP() cannot return an array. You need a filter function for this. This is not yet implemented in LO. But there are some great workarounds which are very well written here.

259218

With kind regards

Juergen

If you want to mark your question as answered, then please click on the small white hook on the answer.

[newbie-02]

@dscheikey: whow!
0.) it works!
00.) it took me about an hour to - nearly - understand
1.) well seen
2.) nice, simple!, intelligent, that’s the sort of things one can stand blocked
3.) i tried adapting the start date formula for the end date (to have two similar formulas and as ‘start’ returned ‘empty’ instead of 30.12.1899 for empty rows) … and failed
3a.) is it lookup? and match? and other ‘search functions’? having a ‘direction’ (left to right, top to bottom) to work through the data what makes it difficult to achieve a ‘mirrored’ result with the opposite criteria (max vs. min)? would it be an improvement if you could specify the ‘search direction’ for ‘searching’ functions?
3b.) for single tasks you can work with a suitable arrangement of the data, two or more queries on the same data set can be more difficult
i’m on the limit of complexity i can follow (especially with namings like ‘if NOT! error’ being ‘iferror’, thus i hope this project doesn’t grow …

[JohnSUN]

Please try this

=SUMPRODUCT(MAX($M$10:$AN$10*(OFFSET($M$11:$AM$11;MATCH(".*";$J$12:$J$21;0);0)<>"")))

[newbie-02]

mmmhhh it’s near target, works with <>"" for text and with <>0 for numbers, but only for some of the ‘indicator cells’, not for all of them …

i’m too short in time at the moment to decode the construction