Absolute Reference in INDEX MATCH doesn't work!?

[Spreadthemsheets]

Hi,


PLEASE HELP!


In my spreadsheet I have the ID of the product I am searching for in column "A" and the Dates across row 7. there are thousands of dates and hundreds of products. I typed this formula to return a specific attribute (in array column #13) and it works in the first cell. but when I try to copy the formula across the next dates the formula updates MATCH(1,(Data!$A$2:$A$1557=A7) to MATCH(1,(Data!$A$2:$A$1557=B7). I tried to fix this with absolute refrence but I get a #N/A error.


{=INDEX(Data!$A$2:$M$1557,MATCH(1,(Data!$A$2:$A$1557=A7)*(Data!$E$2:$E$1557=C6),0),13)}


Does anyone know why i can't use absolute reference and if there is some way I can fix this?

Also posted at http://www.excelforum.com/excel-for...173897-absolute-reference-in-index-match.html

 

[amdopt]

This bold part of your formula: =INDEX(Data!$A$2:$M$1557,MATCH(1,(Data!$A$2:$A$1557=A7)*(Data!$E$2:$E$1557=C6),0),13)

This = Match(1,A7*C6,0)

The second input for Match function is your lookup array. Without knowing what is in your spreadsheet I don't think I can help any further.

 

[Spreadthemsheets]

Hey All,

Thanks so much for your help. It was a STUPID error on my part. I was hitting "ENTER" instead of "CTRL+SHIFT+ENTER".

Thanks again,
Caleb