Help creating a formula to populate a report based on a index & match criteria

[Tbone1234]

Hello,
I need help. I am currently trying to work out a formula where it looks up a value (e.g. tracker #) in one tab, and based on that value it populates my report on another tab. The issue I have is that there will be several values that must populate horizontally, getting them from a vertical table (please see example spreadsheet).
This is the formula I started to try for it, but in only lets me retrieve the first value for each Tracking#.

[Beamernsw]

Hi, I think an array formula is what you need, but I'm not great with them. Hopefully someone else will chime in with a better answer, but until then I have a solution using a helper column.
Paste this into Cell I2 of the "DATA tracker" sheet and copy down:

Please Login or Register  to view this content.

Paste this into Cell D13 of the "RECOVER REPORT" sheet and copy across:

Please Login or Register  to view this content.

[FDibbins]

You have shown the formula you tried, but could you also provide some sample data so we know what else you want?

[Tbone1234]

Just to clarify what I need. If you look at the spreadsheet, there are two tabs "DATA tracker" and "RECOVER REPORT". I want to enter the tracking # (e.g. P1) in tab "RECOVER REPORT" and that all the fields get populated with the data in the tab "DATA tracker". So all the TIME (in 24hr format) populates across from "DATA tracker" to "RECOVER REPORT", same with Size (Reg/Petite) and CROP ID...etc.

I will put in the formulas in the respective cells so that when I enter the tracking #, it looks up the respective values and populates my report "RECOVER REPORT".

[Tbone1234]

Thank you! That works, however, I would love to avoid the Helper column, because the actual data report has way more columns than the example I provided. I have a total of 33 columns in the original table I want to work with. So I really need to try and stay away from helper column. If there is no other way, I will have to do your method which did exactly what I wanted.

[Glenn Kennedy]

In D13, copied across and down:

=IFERROR(INDEX('DATA tracker'!$E:$H,AGGREGATE(15,6,ROW('DATA tracker'!$A$2:$A$17)/('DATA tracker'!$A$2:$A$17=$U$3),COLUMNS($D:D)),ROWS($1:1)),"")

You will need to re-set the format of rows 15 & 16 to general.

[Tbone1234]

In D13, copied across and down:

=IFERROR(INDEX('DATA tracker'!$E:$H,AGGREGATE(15,6,ROW('DATA tracker'!$A$2:$A$17)/('DATA tracker'!$A$2:$A$17=$U$3),COLUMNS($D:D)),ROWS($1:1)),"")

You will need to re-set the format of rows 15 & 16 to general.

AMAZING! Thank you so much! That works perfectly for what I needed.

[Glenn Kennedy]

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.