Formula returning unexpected reference.

[Stephersue]

I am trying to pull a list of equipment from a tasking list that reference an equipment code.

On a seperate tab I have input a count in Cell F6 with:
=COUNTIF('CP Board'!B1:B1999,"*PS")

and starting in A2 I am trying to use :

=IF(ROWS(A$2:A2)>F$1," ",INDEX('CP Board'!B$1:B$2000, SMALL( IF('CP Board'!B$1:B$2000="*PS",ROW('CP Board'!B$1:B$2000)),ROWS(A$2:A2))))

This is a sample of the raw data from the 'CP Board'! tab:

A	B
5RCP <tbody> </tbody>	5RCP 160 01 PS <tbody> </tbody>
5RCP <tbody> </tbody>	5RCP 160 03 CC <tbody> </tbody>
5RCP <tbody> </tbody>	5RCP 160 03 CC <tbody> </tbody>
CT-1 <tbody> </tbody>	CT-1 150 01 PS <tbody> </tbody>
CT-1 <tbody> </tbody>	CT-1 150 03 OS <tbody> </tbody>
CT-1 <tbody> </tbody>	CT-1 150 05 FM <tbody> </tbody>
CT-1 <tbody> </tbody>	CT-1 150 06 LS <tbody> </tbody>
BATM <tbody> </tbody>	BATM 100 01 PS <tbody> </tbody>
BATM <tbody> </tbody>	BATM 100 03 EC <tbody> </tbody>

<tbody>
</tbody>

However Currently it is returning this:

5RCP 160 03 CC <tbody> </tbody>

#NUM! <tbody> </tbody>

#NUM! <tbody> </tbody>

<tbody>
</tbody>

Thoughts Ideas Suggestions... I am going to use the values returned to VLookup equipment descriptions to give me my full equipment list with tonnage, etc.

Thanks for reading!

 

[Aladin Akyurek]

Try rather...

=IF(ROWS(A$2:A2)>F$1,"",INDEX('CP Board'!B$1:B$2000, SMALL(IF(ISNUMBER(SEARCH("PS"&"|",'CP Board'!B$1:B$2000&"|",ROW('CP Board'!B$1:B$2000)-ROW('CP Board'!B$1)+1),ROWS(A$2:A2))))

 

[Stephersue]

Giving me an Error :

"You've entered too many arguments for this function."

 

[Aladin Akyurek]

Try rather...

=IF(ROWS(A$2:A2)>F$1,"",INDEX('CP Board'!B$1:B$2000, SMALL(IF(ISNUMBER(SEARCH("PS"&"|",'CP Board'!B$1:B$2000&"|",ROW('CP Board'!B$1:B$2000)-ROW('CP Board'!B$1)+1),ROWS(A$2:A2))))

Giving me an Error :

"You've entered too many arguments for this function."

=IF(ROWS(A$2:A2)>F$1,"",INDEX('CP Board'!B$1:B$2000,
  SMALL(IF(ISNUMBER(SEARCH("PS"&"|",'CP Board'!B$1:B$2000&"|")),
  ROW('CP Board'!B$1:B$2000)-ROW('CP Board'!B$1)+1),ROWS(A$2:A2))))

 

[Stephersue]

THANK YOU!

It is working perfectly now.

 

[Aladin Akyurek]

THANK YOU!

It is working perfectly now.

You are welcome.