Ask Your Question
0

Calc: Exclude rows from INDEX() reference

asked 2020-10-16 00:18:11 +0100

vxky33x0egwnf4 gravatar image

updated 2020-10-17 20:33:42 +0100

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.

Did not achieve result

Bad workaround

Example worksheet

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

answered 2020-10-16 19:08:05 +0100

m.a.riosv gravatar image

updated 2020-10-17 20:33:48 +0100

A sample that I think does what you like.

image description

Maybe with a pivot table it's easier and can be filtered as you like.

Pivot table sample

edit flag offensive delete link more

Comments

Thanks for your efforts, @m.a.riosv . I am not yet sure this meets my requirements, but I will continue to review it. I have edited my OP to include my own example.

vxky33x0egwnf4 gravatar imagevxky33x0egwnf4 ( 2020-10-17 19:44:17 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2020-10-16 00:18:11 +0100

Seen: 29 times

Last updated: Oct 17