Multi-Value Lookup Error

[Sharly]

Hi,

I have this formula that works just fine:

=IF(ISERROR(SMALL(IF(IF(ISERROR(SEARCH($A$9,$A$1:$A$7)),FALSE,TRUE),ROW($A$1:$A$7)),ROW($C$1:$C$7))),"",INDEX($A$1:$C$7,SMALL(IF(IF(ISERROR(SEARCH($A$9,$A$1:$A$7)),FALSE,TRUE),ROW($A$1:$A$7)),ROW($C$1:$C$7)),3))


My problem is now the search is being done in the same workbook but on a tab called Primary Account with a range of A1 - K3000. Everytime I try to change the lookup the formula gives me an error. It will only work if the information is on the same sheet.

In a nutshell I am looking up data in A5 and want it to lookup anything that has the results of A5 on the Primary Account sheet. A vlookup won't work as I'm looking up multi-values.

Any suggestions?

Thanks!

 

[Crazy billy]

try retyping your formula then use your mouse to click the data(source) on different worksheet. it will capture the name of the other sheet

 

[Sharly]

Thanks. I've done that multiple times but it gives me an error every single time. I thought perhaps I had a typo somewhere in my formula but when I leave it as is it's fine. It's just when I try to switch sheets that the formula just errors out.