CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
Kevin P.
Kevin P.

Reputation: 1053

Unique Filter multiple sheets with ArrayFormula

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)))

enter image description here

Upvotes: 2

Views: 642

Answers (1)

MattKing
MattKing

Reputation: 7773

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.

Upvotes: 2

Related Questions