any Ideas Why this INDEX MATCH formula doesnt work ?

=IFERROR(INDEX(goooooooooo, SMALL(IF(FREQUENCY(IF(goooooooooo<>"", MATCH(ROW(goooooooooo), ROW(goooooooooo)), ""), MATCH(ROW(goooooooooo), ROW(goooooooooo)))>0, MATCH(ROW(goooooooooo), ROW(goooooooooo)), ""), ROW(A1)), COLUMN(A1)), "")


But this works ,

=IFERROR(INDEX(gooooooooo, SMALL(IF(FREQUENCY(IF(gooooooooo<>"", MATCH(ROW(gooooooooo), ROW(gooooooooo)), ""), MATCH(ROW(gooooooooo), ROW(gooooooooo)))>0, MATCH(ROW(gooooooooo), ROW(gooooooooo)), ""), ROW(A1)), COLUMN(A1)), "")


Do you know what might happen , while I press ctrl+shift+enter the first formula brings back blank , while the other brings back the right result . Each one refers to a different array of data

Have you checked that the named range refers to the correct array of data? Other than every cell in the range being blank, I see no reason for the formula to fail.

Yes I ve checked , even thought by accident the same array has a couple other names . Could that be a problem ?

I've not seen that before but could be possible, create a copy of the workbook and delete the duplicate names to see if that fixes it.

Have you tried formula evaluation to see where it fails?

no ,how can I do that?

Select the cell with the formula, go to the 'Formulas' tab on the ribbon and choose 'Evaluate Formula' from the 'Formula Auditing' box.

Follow the underlined section of the formula each time you click the evaluate button to try and find the point where the formula does something unexpected.

=IFERROR(INDEX(goooooooooo, SMALL(IF(FREQUENCY(IF(goooooooooo

it gives me an error value there

The only reason I can think is that you already have an error, or errors in the range that you're referring to.

The results come normally ...maybe Ill try if error

I can't see that working, which error type did formula evaluation return?

#value error

The formula is not at fault, if you're certain that there are no #value errors in the data range that gooooo refers to then the only other thing that could cause it would be the defention of the named range, which I assume is defined by a formula.

Next step would be to look at this in the name manager to see if the error is present and look for a way to correct it there.

I dont know, it doesnt even make sence to me .....

Maybe the workbook is overloaded

I close the subject

Can you attach the workbook? It would be easier to see the problem if I could see the sheet.