Index Match formula not quite working.

[michelle 1]

Hi,

i am struggling with the code below. For the first cell it keeps returning a number that is not in my data, if i drag the cell down all the cells after are correct, just seems to be the first line?

I have added the file so you can see what i am trying to do, i would really apprecaite any help.

Please Login or Register  to view this content.

[AlKey]

Try this array formula

Please Login or Register  to view this content.

***Array formula
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.
You will know the array is active when you see curly braces { } appear around your formula.
If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

[shukla.ankur281190]

tRY

M10=IFERROR(INDEX(Blank1!$F$2:$F$248,MATCH(0,COUNTIF($M$9:M9,IF(Blank1!$F$2:$F$248<>0,Blank1!$F$2:$F$248,"")),0)),"") AND DRAG DOWN

[michelle 1]

Hi AlKey,

i just tried your code but it does not seem to be working, have made sure is entered as an array.

[AlKey]

Hi AlKey,

i just tried your code but it does not seem to be working, have made sure is entered as an array.

You should get rid of the merged cells. COUNTIF(M$8:M8 should be COUNTIF(M$9:M9. Never use merged cells that are involved in formula calculations.

[michelle 1]

Hi Shukla.ankur281190

many thanks just tried and that works perfectly, appreciate your help in resolving.