Help with multiple lookups

Can't you cut down the file size by removing sheets/ranges that aren't vital for showing the problem.
Many of us prefer not to use sites of unproven status

What value were you expecting to get back?

Thanks.

But please clarify what result you want to see in D10:E10.

May be this:

=IFERROR(INDEX('Batters2014-2006'!$A$2:$AA$5654,MATCH(E$4&$A10,'Batters2014-2006'!$D$2:$D$5654&'Batters2014-2006'!$A$2:$A$5654,0),MATCH($A$2,'Batters2014-2006'!$A$1:$V$1,0)),"")

You're trying to match Player ID = 11 followed by Group =1, using a concatenation array. That gives you 111. Excel does find a 111; but it's Player ID=1 and Group 11. Excel can't tell 111 from 111 (not surprisingly) and gives you the wrong answer. In reality there is NO match for Age Group 1, ID 11. See a better formula (no ambiguity and non-array: therefore FASTER) in the two pale green cells.

Note: I cut down the range so that I could follow what was going on. Don't forget to amend back again...

Originally Posted by Josephrandall

It should come back empty

Why,

Please explain the calculation and why the cells you are using for the matches are not relevant.

JosephRandall - I missed your explanation when I posted; but I guess I'm on the right lines... Excel made a mistake 'cos of the ambiguity caused by the concatenation array that you used.

Range concatenation is inefficient (slow to calculate).

Better to use either array multiplication or nested IFs.

Why are problem formulae female????????

Thanks for the reputation - much appreciated.