Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenTue, 10 Apr 2018 09:51:02 +0200Formula to return all corresponding values from an arrayhttps://ask.libreoffice.org/en/question/151641/formula-to-return-all-corresponding-values-from-an-array/ Hello All, I stumbled upon a problem with VLOOKUP that it only returns first value that meets the search criterion, and so does INDEX-MATCH formula. But in my table there are repeating values so my task is to include them all (in, say, ascending order). Here is my table:
![image description](/upfiles/15231056488620913.png)
Where A and B columns contain original data, C is rank, E is filled with =LARGE($B$2:$B$138, C2) to show which B cell is the largest, the second largest and so on. I want D column to return the corresponding number from A column but using =INDEX($A$2:$A$138, MATCH(E2, $B$2:$B$138, 0)) I found that only first match for the criteria is returned. I highlighted wrong pairs of rows with yellow so you can see that the first row of yellow rows is correct while the second returns the first value again instead of finding the next cell containing the criteria (1006 and 1008 respectively). I also highlighted wrong second value with green to make it more prominent.
I suppose column D should contain an array formula instead of INDEX-MATCH but I didn't succeed in creating one. Please help. Thank you!Sat, 07 Apr 2018 15:01:31 +0200https://ask.libreoffice.org/en/question/151641/formula-to-return-all-corresponding-values-from-an-array/Comment by erAck for <p>Hello All, I stumbled upon a problem with VLOOKUP that it only returns first value that meets the search criterion, and so does INDEX-MATCH formula. But in my table there are repeating values so my task is to include them all (in, say, ascending order). Here is my table:</p>
<p><img alt="image description" src="/upfiles/15231056488620913.png"></p>
<p>Where A and B columns contain original data, C is rank, E is filled with =LARGE($B$2:$B$138, C2) to show which B cell is the largest, the second largest and so on. I want D column to return the corresponding number from A column but using =INDEX($A$2:$A$138, MATCH(E2, $B$2:$B$138, 0)) I found that only first match for the criteria is returned. I highlighted wrong pairs of rows with yellow so you can see that the first row of yellow rows is correct while the second returns the first value again instead of finding the next cell containing the criteria (1006 and 1008 respectively). I also highlighted wrong second value with green to make it more prominent. </p>
<p>I suppose column D should contain an array formula instead of INDEX-MATCH but I didn't succeed in creating one. Please help. Thank you!</p>
https://ask.libreoffice.org/en/question/151641/formula-to-return-all-corresponding-values-from-an-array/?comment=151872#post-id-151872Of course MATCH() returns the same position when asked twice for the same value..
I'd simply select the data in columns A and B and sort all on the total B column, menu Data -> Sort...Tue, 10 Apr 2018 01:01:58 +0200https://ask.libreoffice.org/en/question/151641/formula-to-return-all-corresponding-values-from-an-array/?comment=151872#post-id-151872Comment by Sandra90 for <p>Hello All, I stumbled upon a problem with VLOOKUP that it only returns first value that meets the search criterion, and so does INDEX-MATCH formula. But in my table there are repeating values so my task is to include them all (in, say, ascending order). Here is my table:</p>
<p><img alt="image description" src="/upfiles/15231056488620913.png"></p>
<p>Where A and B columns contain original data, C is rank, E is filled with =LARGE($B$2:$B$138, C2) to show which B cell is the largest, the second largest and so on. I want D column to return the corresponding number from A column but using =INDEX($A$2:$A$138, MATCH(E2, $B$2:$B$138, 0)) I found that only first match for the criteria is returned. I highlighted wrong pairs of rows with yellow so you can see that the first row of yellow rows is correct while the second returns the first value again instead of finding the next cell containing the criteria (1006 and 1008 respectively). I also highlighted wrong second value with green to make it more prominent. </p>
<p>I suppose column D should contain an array formula instead of INDEX-MATCH but I didn't succeed in creating one. Please help. Thank you!</p>
https://ask.libreoffice.org/en/question/151641/formula-to-return-all-corresponding-values-from-an-array/?comment=151898#post-id-151898Thank you but sorting is not an option. I need a formula.Tue, 10 Apr 2018 09:51:02 +0200https://ask.libreoffice.org/en/question/151641/formula-to-return-all-corresponding-values-from-an-array/?comment=151898#post-id-151898