I have a project with several sheets. I'm wanting to pull data from one sheet into another if criteria is met. I think I'm close but not sure why it keeps returning blank values.
I put the following formula into sheet #4 and referenced the first sheet by name(Party_Treasure). The data I want to search against is in column H starting on row 9 and down. If column H has the value "Party", I want to pull the data from that sheet in Column A, Row 9 and copy it into my sheet 4 where the formula resides.

Example. On sheet Party_Treasure, Row H9 contains the word Party. I want to return the value in that row, column A, and put it into my other sheet. I'd then like it to continue searching for subsequent listings for "Party" without leaving blank lines on sheet #4

=IFERROR(INDEX(Party_Treasure!H:H,SMALL(IF(Party_Treasure!$H$9:$H$500="Party",ROW(Party_Treasure!$A$9:$A$500)),ROW()-ROW(H$9)+1)),"")