Unique No with conditions

[shukla.ankur281190]

Hi to All,

I am facing a problem with

Please Login or Register  to view this content.

Please help out me.

Sample file is attached for your reference.

Thanks
Ankur

[zbor]

What you trying to do?

[shukla.ankur281190]

I want to extract unique numbers based on condition..

[zbor]

Can you write expected result?

[shukla.ankur281190]

If any of data sheets BL column match with "FC" then this formula

Please Login or Register  to view this content.

should be run for extracting unique numbers. A well as this formula works perfectly for unique numbers but with condition it give me FALSE...

[Tony Valko]

See if you can adapt this example...

Data Range

	A	B	C	D
1	------	------	------	------
2	Fg	1		1
3	Fc	1		3
4	AA	2		5
5	Fc	3
6	Fc	3
7	AD	4
8	Cf	4
9	HG	5
10	Fc	5

This array formula** entered in D2:

=IFERROR(INDEX(B$2:B$10,MATCH(0,IF(A$2:A$10="Fc",COUNTIF(D$1:D1,B$2:B$10)),0)),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).

Hold down both the CTRL key and the SHIFT key
then hit ENTER.Copy down until you get blanks.

[shukla.ankur281190]

Its working perfectly Mr. Tony. can you pls explain why we can not put if condition first??

[Tony Valko]

Because the INDEX function can only return a single result. But, when you put the IF logical test first, it tries to force INDEX to return an array of results.

[shukla.ankur281190]

Oh! then how will I'll know to put the condition in exact location pls.

[Tony Valko]

That usually comes with experience and lots of trial and error.

[shukla.ankur281190]

it means I have to learn too long excel first. ...

Thats O.K you guys will help surly help me for the same. "I Hope"

Thanks once again for help

[Tony Valko]

You're welcome. Thanks for the feedback!

[Richard Buttrey]

What are you wanting to achieve with that formula.

It may be that a Pivot Table would give you any analysis you need.