x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 134

# LARGE INDEX formula

I have one shipment (Tracking #), that has many charges associated with it.
However, they are on two different reports.

I am looking for a large & index combined formula but need some help.
I have attached a workbook with two sheets and sample data.
The LIST sheet has the single instance, but there are many charges associated with that one sale.
The CHARGES sheet shows the charges associated with the sale.
The unique item is the Tracking. I need to pull columns B&C from Charge into the List - matching on the Tracking #.
I need to list them on the same row - Surcharge 1, surcharge 2, surcharge 3....
SAMPLE.xlsx
0
Euro5
• 2
1 Solution

Excel & VBA ExpertCommented:
Try this Array Formula which requires confirmation with Ctrl+Shift+Enter instead of Enter alone.

On List Tab,
In C2
``````=IFERROR(IF(MOD(COLUMNS(\$C2:C2),2)=1,INDEX(Charge!\$B\$2:\$B\$11,SMALL(IF(Charge!\$A\$2:\$A\$11=\$A2,ROW(Charge!\$A\$2:\$A\$11)-ROW(Charge!\$A\$2)+1),COLUMNS(\$C2:C2)-COUNT(\$B2:B2)+1)),INDEX(Charge!\$C\$2:\$C\$11,SMALL(IF(Charge!\$A\$2:\$A\$11=\$A2,ROW(Charge!\$A\$2:\$A\$11)-ROW(Charge!\$A\$2)+1),COLUMNS(\$C2:C2)-COUNTIF(\$B2:B2,"?*")-1))),"")
``````
and then copy across and down until you get blank cells.
SAMPLE.xlsx
0

Author Commented:
Awesome thank you!!
0

Excel & VBA ExpertCommented:
You're welcome.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.