Another Excel Question for the Gurus (1 Viewer)

[dal_boys_phan]

Not sure what I am doing wrong, but here it goes...

Working on a baseball draft spreadsheet with two named ranges with data - AuctionDraft_Data and ReserveDraft_Data - along with a named range for headers - Data_Table_Headers.

Using an array formula to pull from the tab where I'll keep the auction draft results onto another tab where I have teams to list each team draftee. Trying to put all the Auction Draft results in one section (starting in cell B3) and all the Reserve Draft results in an adjoining section (starting in cell G3). Team name used to reference back to each set of data is in B1, my row counter for use with each section starts in A4 (for 23 rows), and my table column headers start in B3 (Auction) and G3 (Reserve) respectively.

This formula for the auction draft data works...

{=IFERROR(INDEX(AuctionDraft_Data,SMALL(IF(AuctionDraft_Data=$B$1,ROW(AuctionDraft_Data)),$A4),MATCH(B$3,Data_Table_Headers,0)),"")}

This formula for the reserve draft data DOES NOT work

{=IFERROR(INDEX(ReserveDraft_Data,SMALL(IF(ReserveDraft_Data=$G$1,ROW(ReserveDraft_Data)),$A4),MATCH(G$3,ResData_Headers,0)),"")}

I cannot figure out what is wrong...so far I tried the following:

• creating a new range for headers - ResData_Headers - to make sure the headers I was referencing were in the range (ReserveDraft_Data) I was using
• used Formulas > Use In Formula to make sure the names of the data ranges are correct
• copied the good formula over and modified to work with the reserve data
• made sure the data set was sound
• rechecked my data ranges
• used data validation to make sure it was not a spelling issue

And finally, yes I am using CNTRL+SHIFT+ENTER to ensure that the formula goes in as an array.

Anyone see something that I am missing here...

 

[tjnc09]

I think sometimes copying an array formula doesn't work well.

I would try manually typing (don't copy Auction) your Reserve formula in and hitting CSE

 

[dal_boys_phan]

I think sometimes copying an array formula doesn't work well.

I would try manually typing (don't copy Auction) your Reserve formula in and hitting CSE

Thanks. I'll give that a try.