Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenSat, 22 Aug 2020 14:58:36 +0200can vlookup return an array? easy way to find one value depending on multiple other entrieshttps://ask.libreoffice.org/en/question/261157/can-vlookup-return-an-array-easy-way-to-find-one-value-depending-on-multiple-other-entries/hello @all,
i'm working in a quite complex sheet from which i have a reduced version here: [vlookup_array_wish.ods](/upfiles/15979001725571335.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 https://ask.libreoffice.org/en/question/242233/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 ...)Thu, 20 Aug 2020 07:19:58 +0200https://ask.libreoffice.org/en/question/261157/can-vlookup-return-an-array-easy-way-to-find-one-value-depending-on-multiple-other-entries/Answer by dscheikey for <p>hello @all, </p>
<p>i'm working in a quite complex sheet from which i have a reduced version here: <a href="/upfiles/15979001725571335.ods">vlookup_array_wish.ods</a></p>
<p>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, </p>
<p>my wish is an easy and 'editable' solution for the following task: </p>
<p>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, </p>
<p>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, </p>
<p>but it fails when the work contains special info like in AC15:AE15, </p>
<p>(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), </p>
<p>as working with 'SUM' special info like 'FV' blocks the formula, i can change this with more nested ifs, but ... that's not elegant ... </p>
<p>i'd like to have a shorter / easier solution which is more handy for edits (like now adding 'FV'),</p>
<p>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 ... </p>
<p>anybody a good idea? </p>
<p>imho <a href="https://ask.libreoffice.org/en/question/242233/vlookup-is-not-adding-correctly/">https://ask.libreoffice.org/en/questi...</a> deals with a similar request, but regarding the complexity of that thread i'm off from digging into it ... </p>
<p>(any other enhancement welcome ...)</p>
https://ask.libreoffice.org/en/question/261157/can-vlookup-return-an-array-easy-way-to-find-one-value-depending-on-multiple-other-entries/?answer=261398#post-id-261398Hi 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.
[C:\fakepath\can vlookup return an array.ods](/upfiles/15980008238681384.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](https://ask.libreoffice.org/en/question/259218/functionformula-to-filter/?answer=259455#post-id-259455)
With kind regards
Juergen
If you want to mark your question as answered, then please click on the small white hook on the answer.Fri, 21 Aug 2020 11:22:04 +0200https://ask.libreoffice.org/en/question/261157/can-vlookup-return-an-array-easy-way-to-find-one-value-depending-on-multiple-other-entries/?answer=261398#post-id-261398Comment by newbie-02 for <p>Hi newbie-02,</p>
<p>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.</p>
<p><a href="/upfiles/15980008238681384.ods">C:\fakepath\can vlookup return an array.ods</a></p>
<p>1.) JohnSUN had not considered your requirement that "FV" or numbers represent the beginning. This is now corrected.</p>
<p>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. </p>
<p>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. </p>
<p>If you have any questions, please contact me via comment.</p>
<p>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.</p>
<p><a href="https://ask.libreoffice.org/en/question/259218/functionformula-to-filter/?answer=259455#post-id-259455">259218</a></p>
<p>With kind regards</p>
<p>Juergen</p>
<p>If you want to mark your question as answered, then please click on the small white hook on the answer.</p>
https://ask.libreoffice.org/en/question/261157/can-vlookup-return-an-array-easy-way-to-find-one-value-depending-on-multiple-other-entries/?comment=261620#post-id-261620@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 ...Sat, 22 Aug 2020 14:58:36 +0200https://ask.libreoffice.org/en/question/261157/can-vlookup-return-an-array-easy-way-to-find-one-value-depending-on-multiple-other-entries/?comment=261620#post-id-261620Answer by JohnSUN for <p>hello @all, </p>
<p>i'm working in a quite complex sheet from which i have a reduced version here: <a href="/upfiles/15979001725571335.ods">vlookup_array_wish.ods</a></p>
<p>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, </p>
<p>my wish is an easy and 'editable' solution for the following task: </p>
<p>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, </p>
<p>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, </p>
<p>but it fails when the work contains special info like in AC15:AE15, </p>
<p>(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), </p>
<p>as working with 'SUM' special info like 'FV' blocks the formula, i can change this with more nested ifs, but ... that's not elegant ... </p>
<p>i'd like to have a shorter / easier solution which is more handy for edits (like now adding 'FV'),</p>
<p>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 ... </p>
<p>anybody a good idea? </p>
<p>imho <a href="https://ask.libreoffice.org/en/question/242233/vlookup-is-not-adding-correctly/">https://ask.libreoffice.org/en/questi...</a> deals with a similar request, but regarding the complexity of that thread i'm off from digging into it ... </p>
<p>(any other enhancement welcome ...)</p>
https://ask.libreoffice.org/en/question/261157/can-vlookup-return-an-array-easy-way-to-find-one-value-depending-on-multiple-other-entries/?answer=261264#post-id-261264Please try this
=SUMPRODUCT(MAX($M$10:$AN$10*(OFFSET($M$11:$AM$11;MATCH(".*";$J$12:$J$21;0);0)<>"")))Thu, 20 Aug 2020 16:33:44 +0200https://ask.libreoffice.org/en/question/261157/can-vlookup-return-an-array-easy-way-to-find-one-value-depending-on-multiple-other-entries/?answer=261264#post-id-261264Comment by newbie-02 for <p>Please try this</p>
<pre><code>=SUMPRODUCT(MAX($M$10:$AN$10*(OFFSET($M$11:$AM$11;MATCH(".*";$J$12:$J$21;0);0)<>"")))
</code></pre>
https://ask.libreoffice.org/en/question/261157/can-vlookup-return-an-array-easy-way-to-find-one-value-depending-on-multiple-other-entries/?comment=261525#post-id-261525mmmhhh 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 :-(Fri, 21 Aug 2020 22:29:08 +0200https://ask.libreoffice.org/en/question/261157/can-vlookup-return-an-array-easy-way-to-find-one-value-depending-on-multiple-other-entries/?comment=261525#post-id-261525