Youtube Excel Magic Trick 538 - Question

[LongCR]

http://www.youtube.com/watch?v=tqCEY5YMyqw

I am trying to duplicate the concept on my spreadsheet. It is conceptually the same, the formula is slightly different.

Mine is as follows:
{=IF(ROWS(A$5:A5)>$B$2,"",
INDEX(US!A$5:A$72,
SMALL(IF(US!$AU$5:$AU$72=Sheet1!$A$2,ROW(US!$AU$5:$AU$72)-ROW(US!$AU$5)+1),ROWS(A$5:A5))))}

Resulting in a #VALUE! error. Can anybody advise me what should the formula me?

Thank a million!
Long

 

[Domenic]

The formula needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER. If done correctly, Excel will automatically place curly braces {...} around the formula.

 

[LongCR]

Thanks for your reply.

Yes I did that. Control + Shift + Enter. It still has the error.

Can anybody help? And do a comparison with the formula in the Youtube? Thanks.

Best wishes,
Long

 

[Domenic]

Do any of the cells in the data contain the error #VALUE! ?

 

[LongCR]

Hi Dom,

You are sooo right. Thank you very much for this!!!

I spend the half my day trying to solve this!!



With metta,
Long