Index Indirect Match problem

I come to you for help once again.

I'm probably being thick, but I can't get the following formula to work:

=INDEX(INDIRECT($A$6),MATCH($A$7,$A$21:$A$24,0),MATCH(B$6,$B$20:$M$20,0))

I've placed it in a work book to retrieve annual data based on selections from combo boxes but I can't get it to work!

I include the worksheet to show you what I'd like.

Help!

Steve

If you are going to reference the table indirectly, you need to apply names to those tables to match.

So select B21:M24 and name it PTS (type PTS in the Name Box just to the left of the Formula Bar). Select B28 and M31 and name it WOL

Then you need to make the 2011 entry in A7 an actual number.. it is currently entered as text. Go to the cell and hit F2, then Enter.

General Format is ok... it just was entered it seems originally as a text string....

How is it affecting the result?

Strange.. it is fine in the attachment you sent...

Anyway, first make sure that the format is General... then go to Data|Text to columns and just hit Finish.

This should keep it in the General format...

If all else fails, use formula:

=INDEX(INDIRECT($A$6),MATCH($A$7+0,$A$21:$A$24,0),MATCH(B$6,$B$20:$M$20,0))

Ok... it's the combo box causing that (i overlooked that previously... I was just changing the year manually)... try the last formula revision I gave you:

=INDEX(INDIRECT($A$5),MATCH($A$7+0,$A$29:$A$32,0),MATCH(B$6,$B$28:$M$28,0))

copied across....