If function with index and match not giving the data I need

[perkinsr1]

Hi,

I am using the below formula to try and get the the following information:

I am asking it to look for a name in the left cell of the main sheet and then look at the 1st column of the 2nd sheet to find it, then index the 2nd sheet data and match "Two Man" in the 2nd column of the 2nd sheet and then return the 48th column data. I get data but it is from the 48th column of the first person on the 2nd report???

Hope this makes sense?

A10 is the advisor name on the 1st report

INDEX and MATCH are both on the 2nd report

=IF('[Report Name 1]Wk 06'!$A:$A,$A10,INDEX('[Report Name 2]Wk 06'!$B:$AX,MATCH("Two Man",'[Report Name 2]Wk 06'!$B:$B,0),48))

 

[LloydFinancials]

So, if I have "Two Man" on the 19th row of '[Report Name 2]Wk 06', I get the value in AW19. It seems to be working for me.

 

[perkinsr1]

Hi,

That is strange, I have it looking for the name A10 from '[Report Name 1]'Wk 06' on '[Report Name 2]Wk 06', I get the value from the 1st advisor which is row 6 and then the data in AW6, this is baffling.

 

[LloydFinancials]

Can you add links to your workbooks, or some innocuous sample of each with your failing formula?

 

[perkinsr1]

https://drive.google.com/open?id=1ZlQlRLhMsCsdgQtDih7DMzX0adbvWEho

https://drive.google.com/open?id=15P6iPyS35je798AtlCBSFrd8kONl-EtG


Hi, Sorry for the delay I have been working away, I hope you can access the above links. I have copied the formula in report 1, as you can see the issue is when it is copied down, it will only bring back the same data for advisor 1.

Hope that makes sense?

Thanks again

Rob