LibreOffice Calc: Exclude rows from INDEX() reference

Working on Sheet2, I can use the following formula to look up the "Sheet2.B1"th match in Sheet1, column B corresponding to Sheet1, column C:

{=INDEX($Sheet1.$B$1:$B$9,SMALL(IF($Sheet1.$C$1:$C$9=$A2,ROW($Sheet1.$C$1:$C$9)-ROW($Sheet1.$C$1)+1),B$1))}

I'd like to exclude rows from the lookup conditioned on the corresponding values in Sheet1, column A. For example, IF $Sheet1.$A$1:$A$9=0 THEN *exclude from row array*. I can achieve this by using Data > More Filters > Standard Filter..., copying the filtered rows to a new sheet, and using the formula above on it. However, I'd like to manage without duplicating any data.

Thanks in advance.

Edit: For example, I'd like to achieve the result in Sheet2_1 without having to filter and copy the data in Sheet1 to Sheet1_1.

Example worksheet

1 Answer

Try using the following formula on Sheet2.B2:

{=IFERROR(INDEX($Sheet1.$B$1:$B$9,IF(INDIRECT("$Sheet1.A" & SMALL(IF($Sheet1.$C$1:$C$9=$A2,ROW($Sheet1.$C$1:$C$9)-ROW($Sheet1.$C$1)+1),B$1)) =1, SMALL(IF($Sheet1.$C$1:$C$9=$A2,ROW($Sheet1.$C$1:$C$9)-ROW($Sheet1.$C$1)+1),B$1) ,"")),"")}

It checks if the row in column A of Sheet1 is 0 or 1. If 0, an empty string is returned. If 1, the value of the cell in column B of Sheet1 is returned. Then the based on the index, the row value is returned or an empty string in case of an error related to setting an empty string for 0.

Note that it is an array formula. Use the same logic for Sheet2_1.

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy