Need help (formula returns no result)

[mahalek1976]

{=ifiserror(INDEX('Usable Data'!$B$2:$M$201,SMALL(IF('UsableData'!$M$2:$M$201='Working Staff'!$D$1,ROW('UsableData'!$M$2:$M$201)-1),ROW(A1)),3),"")}

This formula is returning no result

 

[AliGW]

It should be IFERROR, not IFISERROR - does that make a difference?

If not, remove the error trap and tell us what it then returns.

 

[mahalek1976]

it does not make a difference.

 

[AliGW]

Then do what I suggested: remove the error trap and tell us what you get - what error message?

 

[mahalek1976]

ADVERTISEMENT

#VALUE!

<colgroup><col width="498" style="width: 374pt; mso-width-source: userset; mso-width-alt: 18212;"> <tbody> </tbody>

 

[Joe4]

Cross-posted: HElp WIth This formula

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule #13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[63falcondude]

ADVERTISEMENT

The reason why your formula is returning nothing is because the formula contains an error.

=IFERROR(formula,"") will return nothing if the formula results in an error.
=IFERROR(formula,"popcorn") will return popcorn if the formula results in an error.

You have to find out why INDEX('Usable Data'!$B$2:$M$201,SMALL(IF('UsableData'!$M$2:$M$201='Working Staff'!$D$1,ROW('UsableData'!$M$2:$M$201)-1),ROW(A1)),3) is giving you an error. This should be an array formula. Without looking too much into it, try =INDEX('Usable Data'!$B$2:$M$201,SMALL(IF('UsableData'!$M$2:$M$201='Working Staff'!$D$1,ROW('UsableData'!$M$2:$M$201)-1),ROW(A1)),3) Ctrl Shift Enter instead of just Enter.

 

[mahalek1976]

Thanks for your help so far. The column M that I am looking in for the letter W is actually returning W from a formula. Does this matter?

 

[Marcelo Branco]

{=ifiserror(INDEX('Usable Data'!$B$2:$M$201,SMALL(IF('UsableData'!$M$2:$M$201='Working Staff'!$D$1,ROW('UsableData'!$M$2:$M$201)-1),ROW(A1)),3),"")}

This formula is returning no result

1. Check the sheet names: it seems is missing a space in UsableData or there is a wrong space in 'Usable Data"
2 . The formula shoud be (assuming 'Usable Data' is correct)
=IFERROR(INDEX('Usable Data'!$B$2:$M$201,SMALL(IF('Usable Data'!$M$2:$M$201='Working Staff'!$D$1,ROW('Usable Data'!$M$2:$M$201)-ROW('Usable Data'!$M$2)+1,ROWS(A$1:A1)),3),"")

M.