Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# LARGE INDEX formula

Posted on 2016-08-03
Medium Priority
118 Views
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
Question by:Euro5
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 2

LVL 32

Accepted Solution

Subodh Tiwari (Neeraj) earned 2000 total points
ID: 41741723
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 Closing Comment

ID: 41742698
Awesome thank you!!
0

LVL 32

Expert Comment

ID: 41742706
You're welcome.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

My attempt to use PowerShell and other great resources found online to simplify the deployment of Office 365 ProPlus client components to any workstation that needs it, regardless of existing Office components that may be needing attention.
I was prompted to write this article after the recent World-Wide Ransomware outbreak. For years now, System Administrators around the world have used the excuse of "Waiting a Bit" before applying Security Patch Updates. This type of reasoning to me …
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
###### Suggested Courses
Course of the Month4 days, 19 hours left to enroll