# Youtube Excel Magic Trick 538 - Question

This is a discussion on Youtube Excel Magic Trick 538 - Question within the Excel Questions forums, part of the Question Forums category; 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 ...

1. ## Youtube Excel Magic Trick 538 - Question

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

2. ## Re: Youtube Excel Magic Trick 538 - Question

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.

3. ## Re: Youtube Excel Magic Trick 538 - Question

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

4. ## Re: Youtube Excel Magic Trick 538 - Question

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

5. ## Re: Youtube Excel Magic Trick 538 - Question

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

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•