I have an INDEX MATCH formula that worked fine in Excel 2007 that is not working in Excel 2010.
The INDEX part seems to be OK, the issue comes in with the match function.
The formula I'm using is this:
INDEX(NEFut[NumericClosingPrice], MATCH(1,(VLOOKUP(F387, Lookup!H:I, 2, FALSE)=NEFut[Combo])*(H387&I387=NEFut[ContractYearMonth]),0))
The references to "NEFut" come from a table on a separate tab. These ranges are the same size.
On my spreadsheet, the formula returns the #VALUE! error. When I use the evaluate formula function, it finds the range "NEFut[NumericClosingPrice]", it is able to do the VLOOKUP and return the correct value, but it returns the #VALUE! error when looking for the "NEFut[Combo]" range and the "NEFut[ContractYearMonth]" range.
Any assistance would be appreciated!
The INDEX part seems to be OK, the issue comes in with the match function.
The formula I'm using is this:
INDEX(NEFut[NumericClosingPrice], MATCH(1,(VLOOKUP(F387, Lookup!H:I, 2, FALSE)=NEFut[Combo])*(H387&I387=NEFut[ContractYearMonth]),0))
The references to "NEFut" come from a table on a separate tab. These ranges are the same size.
On my spreadsheet, the formula returns the #VALUE! error. When I use the evaluate formula function, it finds the range "NEFut[NumericClosingPrice]", it is able to do the VLOOKUP and return the correct value, but it returns the #VALUE! error when looking for the "NEFut[Combo]" range and the "NEFut[ContractYearMonth]" range.
Any assistance would be appreciated!
