INDEX MATCH Function

[puneetsod]

Hi All,

I am looking for a solution to an excel problem.

I have data for sales representatives showing the different states they are selling products in, the different states are randomly sorted in rows, ie- Agent A is rendering services in New York Michigan and Utah, they are US Territory 1, 2 and 3 respectively, but for Agent 2 rendering services in California, New York and Montana they are US Territory 1, 2 and 3. I have different IDs for the agents so that is a unique reference. In my output sheet I have all the US states in alphabetical order I need the states for those states arranged in columns against those agents. I am currently able to do a INDEX MATCH and can bring in the US territories as in Terriotry 1 , 2 and 3 and they are randomly ordered for all agents, but i need them ordered in columns against the respective states i have in the column.

 

[AliGW]

Which version of Excel are you using? Where should overseas territories be listed?

 

[puneetsod]

Office 365. In a separate tab. Refer attached file.

 

[AliGW]

Please ...

 

[puneetsod]

Please

 

[AliGW]

I am not sure that I understand. In the workbook you have an INDEX MATCH formula that lists US states AND overseas territories (Sheet 1) - is this where you want a new formula? And if it is, and you want the states to align with the states as listed in B and C (is this correct?), where should China et al be listed?

 

[puneetsod]

Yes please. need the states aligned to the states listed in col BI to DU in tab Rep Hunter to the states listed in Sheet 1 in Col B10 to B66

Some anomalies in the data shows China and overseas territory listed there which I need to ignore.

Thanks.

 

[AliGW]

First, get rid of ALL of the INDEX MATCH formulae (they are clunky and slow). We'll use 365 functions instead.

Next, in D10 copied across:

=LET(d,TOCOL(FILTER('RepHunter Contacts - Member #12'!$BI2:.$DW5000,('RepHunter Contacts - Member #12'!$C2:.$C5000=D$2)),1),
BYROW($B$10:$B$66,LAMBDA(r,XLOOKUP(r,d,d,""))))

 

[puneetsod]

Amazing. works like a charm. Thanks a lot. Couldn't have worked better. Learnt something new. Thanks again

 

[AliGW]

You're welcome! Please don't forget to hit the LIKE button under my post. Thanks.

 

[p45cal]

Now that I understand what you want (thanks @AliGW !) there's another formula that you could use which also means you don't need the TEXTSPLIT in column BI of the first sheet. In D10:

=BYROW($B10:$B66,LAMBDA(a,IF(ISNUMBER(SEARCH(a,XLOOKUP(D2,'RepHunter Contacts - Member #12'!$C2:.$C$5000,'RepHunter Contacts - Member #12'!$BC2:.$BC5000))),a,"")))

copied across.