Cell gives incorrect value when I press ENTER.

**Matthew.Newlove** · 04-25-2012, 10:13 AM

I have the following formula in a cell:

=INDEX(AE$3:AX$22,MAX(IFERROR(MATCH("*"&LEFT(BD14,ROW(INDEX(BI:BI,1):INDEX(BI:BI,LEN(BD14))))&"*",AD$3:AD$22,0),-1)),MAX(IFERROR(MATCH("*"&LEFT(BF14,ROW(INDEX(BI:BI,1):INDEX(BI:BI,LEN(BF14))))&"*",AE$2:AX$2,0),-1)))

If I select the cell and press enter at the end of the formula, the cell displays an incorrect value, despite the formula being correct and initially displaying the correct result, this also happens on other cells with the same formula but different input cells, again displaying an incorrect value until I select it and press enter. I'm sure there is a very simple solution, but I just can't find it!
Thank you in advance for your help.

**DGagnon** · 04-25-2012, 10:17 AM

This appears to be an array formula, instead of pressing enter, try pressing Ctrl+Shift+Enter

Edit:

on second thought, it dosnt appear to be array... could you attach the workbook that it should be used in?

**Matthew.Newlove** · 04-25-2012, 10:34 AM

Index-Match.xlsx

The formula is in Column BI, upon reviewing, the formula itself is actually returning incorrect results in a number of cells, but that I believe is a different matter which I shall tackle once my curiosity about this problem is cured.

**DGagnon** · 04-25-2012, 10:36 AM

Yes this is an array formula, please try my first suggestion... when run as a standard formula in BI5 you will get 3.36, as an array you get 3.24

but i must admit im not really sure what the objective of the formula is.

**Matthew.Newlove** · 04-25-2012, 10:58 AM

Hello, yes that's solved it thank you, as suspected it was a very simple solution! The formula is somewhat complex (to me anyway), it is to find the specific cell which corresponds to the team in column BD to the team on the left column of the table, and the team in BF to the team on the top row of the table and give's me the cell which then corresponds to both of these within the table, when the teams in both BD and BF are variable. It's actually just me being lazy making excel pull this information out of the table to save me having to look through it. Thank you for your help.