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:

CodePudding user response：

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.