INDEX/MATCH not working

[perkinsr1]

Hi,
I am using INDEX/MATCH to look for the team leaders name in row 1 and then to return the advisors name from the list below it, when I use this I get data from the first or second column relating to a different TL name.

Any ideas what I am doing wrong? I have attached a pic of what I have.

 

[Fluff]

Very difficult to tell what might be wrong, as all you've done is post an image of some data.

 

[perkinsr1]

Very difficult to tell what might be wrong, as all you've done is post an image of some data.

Oops, it would help if I put the formula on, I am using the below:

=IFERROR(INDEX(Data!A1:CQ20,MATCH(A2,Data!A1:CQ1,0)+1,0),0)

Data is a tab with the TL names in row1 with the advisors names below each TL
A2 is the cell I put the TL name in to return the desired result
A1:CQ20 is the area I am looking in to return the advisors details.

Hope that makes sense?

Thanks again

Rob

 

[Fluff]

Ok, thanks for that, what version of Excel are you using?
You can modify your account details to show you version & platform, which saves members having to ask.

 

[perkinsr1]

Thanks, I will have a look at that, I am using 2016

 

[Fluff]

Ok, assuming the formula is in B2 copied down, try
=IFERROR(INDEX(Data!$A$1:$CQ$20,ROWS(B$1:B2),MATCH($A$2,Data!$A$1:$CQ$1,0)),0)

 

[perkinsr1]

Hi Fluff,
That has worked perfect, thank you for the support, legend as ever.

 

[Fluff]

You're welcome & thanks for the feedback.