Unique Filter multiple sheets with ArrayFormula

Question

I have no idea how to title this post, apologize in advance.

I have several sheets with a number in Column I and a name centered and merged in columns A:H. I want to obtain the name from A:H of the corresponding value within I but do have duplicates, therefore I need the nth value when permitted. The formula I have so far works up to the point it does not autofill down as an ArrayFormula, so when I drag the formula down I get an #REF! error due to the fact that when a duplicate is found it cannot overwrite the formula below.

This will be easier to showcase: LINK TO SHEET.

Essentially, in the main sheet all the values in I:I of all the other sheets are obtained and sorted, then using that column I want to return the name that corresponds to the value, allowing for duplicates to work themselves out. I believe my issues resides in the $B1 part at the end of the formula preventing it from being an array.

=ARRAYFORMULA(UNIQUE(FILTER({Sheet2!$A$1:$A;Sheet3!$A$1:$A;Sheet4!$A$1:$A},{Sheet2!$I$1:$I;Sheet3!$I$1:$I;Sheet4!$I$1:$I}=$B1)))

MattKing · Accepted Answer · 2022-01-03 21:57:54Z

2

Cell F2 on the Sheet1 tab:

=QUERY({Sheet2!A:I;Sheet3!A:I;Sheet4!A:I},"select Col1,Col9 where Col9>0 order by Col9 asc",0)

You can read more about query here.

edited Jan 3, 2022 at 21:57

answered Jan 3, 2022 at 20:47

MattKing

7,80310 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Kevin P. Over a year ago

If you would kindly update the formula to reflect the final solution (containing the ">0") I will mark as solution! Just didn't know if you could edit a solution once marked.

MattKing Over a year ago

@KevinP. Good call. Done!

Collectives™ on Stack Overflow

Unique Filter multiple sheets with ArrayFormula

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related