Formula to find the intersection of two indices

[tomshepstone]

I am using the following =IF(EQ$4="","",(INDEX('Trainer List'!$A$1:$MS$93,MATCH($A99,'Trainer List'!$E$1:$MS$1,0),MATCH(EQ$4,'Trainer List'!$A$1:$A$95,0)))) in order to deliver the cell value of two interescting lists. For some reason, the formula has stopped working after 99 repetitions returning a #REF! result. The forumla above works, however this one doesn't and returns the #REF! =IF(EQ$4="","",(INDEX('Trainer List'!$A$1:$MS$93,MATCH($A100,'Trainer List'!$E$1:$MS$1,0),MATCH(EQ$4,'Trainer List'!$A$1:$A$95,0))))

I would appreciate any help or advice on the matter.

[h2ttp]

i didn't see the #REF! result.
perhaps because you neglected to attach the worksheet containing the 2 formulas.

[Jonmo1]

I believe you have your match functions reversed.

Should be
=IF(EQ$4="","",(INDEX('Trainer List'!$A$1:$MS$93,MATCH(EQ$4,'Trainer List'!$A$1:$A$95,0),MATCH($A100,'Trainer List'!$E$1:$MS$1,0))))

[AliGW]

Welcome to the forum! Please take a moment to re-read forum rule #1 and then amend your thread title to something that better explains your problem. Changing your thread title is not optional, which means you must change it. Thanks!

• Use concise, accurate thread titles.
  
• Your post title should describe your problem, not your anticipated solution.
• Use terms appropriate to a Google search - poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice tell us nothing.
• Responding to a request to change your thread title by doing so is mandatory.

To change a title go to your first post, click EDIT then Go Advanced and change your title.

No help to be offered, please, until the OP complies with this request.

[tomshepstone]

Apologies, trust my amends are acceptable

[AliGW]

Very good - much better, thanks!

[tomshepstone]

Hey Jonmo, thanks for the tip.

While this has corrected that error, it is now reporting data from two columns earlier. i.e. should return the value in column E but is returning column C, likewise should be F returning D etc...

[AliGW]

Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

[Jonmo1]

Yes, an attached workbook would be very helpful.
But I can see a couple needed corrections.

The columns of the 2nd match need to match the columns of the index
The rows of the 1st match need to match the rows of the index

And need to reverse the absolute referencing of the 2 lookup values EQ4 and A100

=IF(EQ$4="","",(INDEX('Trainer List'!$A$1:$MS$93,MATCH(EQ$4,'Trainer List'!$A$1:$A$95,0),MATCH($A100,'Trainer List'!$E$1:$MS$1,0))))
should be
=IF(EQ$4="","",(INDEX('Trainer List'!$A$1:$MS$93,MATCH($EQ4,'Trainer List'!$A$1:$A$93,0),MATCH(A$100,'Trainer List'!$A$1:$MS$1,0))))