Lookup returning one more than expected
 

Hi,

I am using a formula to return multiple values relating to a single value ie
Sheet5
CA CB
001 John, Smit
001 Jane Seymore
001 John
002 Chris

Workings sheet returns:

001 John, Smit
001 Jane Seymore
001 John
002 Chris
- -

The problem I get is that one extra value ie Chris being returned even
though Chris does not fall in the criteria(001).

=IF(ISERROR(INDEX(Sheet5!$A$2:$B$185,SMALL(IF(Shee t5!$A$2:$A$185=Workings!$B$6,ROW(Sheet5!$A$2:$A$18 5)),ROW(1:1)),2)),"-",INDEX(Sheet5!$A$2:$B$185,SMALL(IF(Sheet5!$A$2:$A $185=Workings!$B$6,ROW(Sheet5!$A$2:$A$185)),ROW(1: 1)),2))

Please let me know if there is something I can do

Lookup returning one more than expected
 

Try this (array entered):

=IF(ROWS($1:1)<=COUNTIF(Sheet5!$A$2:$A$185,Working s!$B$6),INDEX(Sheet5!$B$2:$B$185,SMALL(IF(Sheet5!$ A$2:$A$185=Workings!$B$6,ROW(A$2:A$185)-ROW(A$2)+1),ROWS($1:1))),"-")

Biff

"Victor" wrote in message
...
Hi,

I am using a formula to return multiple values relating to a single value
ie
Sheet5
CA CB
001 John, Smit
001 Jane Seymore
001 John
002 Chris

Workings sheet returns:

001 John, Smit
001 Jane Seymore
001 John
002 Chris
- -

The problem I get is that one extra value ie Chris being returned even
though Chris does not fall in the criteria(001).

=IF(ISERROR(INDEX(Sheet5!$A$2:$B$185,SMALL(IF(Shee t5!$A$2:$A$185=Workings!$B$6,ROW(Sheet5!$A$2:$A$18 5)),ROW(1:1)),2)),"-",INDEX(Sheet5!$A$2:$B$185,SMALL(IF(Sheet5!$A$2:$A $185=Workings!$B$6,ROW(Sheet5!$A$2:$A$185)),ROW(1: 1)),2))

Please let me know if there is something I can do

Lookup returning one more than expected
 

Thanks Biff

Works 100%!

"Biff" wrote:

Try this (array entered):

=IF(ROWS($1:1)<=COUNTIF(Sheet5!$A$2:$A$185,Working s!$B$6),INDEX(Sheet5!$B$2:$B$185,SMALL(IF(Sheet5!$ A$2:$A$185=Workings!$B$6,ROW(A$2:A$185)-ROW(A$2)+1),ROWS($1:1))),"-")

Biff

"Victor" wrote in message
...
Hi,

I am using a formula to return multiple values relating to a single value
ie
Sheet5
CA CB
001 John, Smit
001 Jane Seymore
001 John
002 Chris

Workings sheet returns:

001 John, Smit
001 Jane Seymore
001 John
002 Chris
- -

The problem I get is that one extra value ie Chris being returned even
though Chris does not fall in the criteria(001).

=IF(ISERROR(INDEX(Sheet5!$A$2:$B$185,SMALL(IF(Shee t5!$A$2:$A$185=Workings!$B$6,ROW(Sheet5!$A$2:$A$18 5)),ROW(1:1)),2)),"-",INDEX(Sheet5!$A$2:$B$185,SMALL(IF(Sheet5!$A$2:$A $185=Workings!$B$6,ROW(Sheet5!$A$2:$A$185)),ROW(1: 1)),2))

Please let me know if there is something I can do

Lookup returning one more than expected
 

You're welcome. Thanks for the feedback!

Biff

"Victor" wrote in message
...
Thanks Biff

Works 100%!

"Biff" wrote:

Try this (array entered):

=IF(ROWS($1:1)<=COUNTIF(Sheet5!$A$2:$A$185,Working s!$B$6),INDEX(Sheet5!$B$2:$B$185,SMALL(IF(Sheet5!$ A$2:$A$185=Workings!$B$6,ROW(A$2:A$185)-ROW(A$2)+1),ROWS($1:1))),"-")

Biff

"Victor" wrote in message
...
Hi,

I am using a formula to return multiple values relating to a single
value
ie
Sheet5
CA CB
001 John, Smit
001 Jane Seymore
001 John
002 Chris

Workings sheet returns:

001 John, Smit
001 Jane Seymore
001 John
002 Chris
- -

The problem I get is that one extra value ie Chris being returned even
though Chris does not fall in the criteria(001).

=IF(ISERROR(INDEX(Sheet5!$A$2:$B$185,SMALL(IF(Shee t5!$A$2:$A$185=Workings!$B$6,ROW(Sheet5!$A$2:$A$18 5)),ROW(1:1)),2)),"-",INDEX(Sheet5!$A$2:$B$185,SMALL(IF(Sheet5!$A$2:$A $185=Workings!$B$6,ROW(Sheet5!$A$2:$A$185)),ROW(1: 1)),2))

Please let me know if there is something I can do