# Can you explain how this formula works?

1. ## Can you explain how this formula works?

Hi all the experts,

I had received a with the following array formula, and couldn't understand how it works:

=IFERROR(INDEX(\$D\$2:\$D\$91,MATCH(0,COUNTIF(\$F\$1:F1,\$D\$2:\$D\$91),0)),"")

Is anyone able to explain to me? we can ignore the IFERROR as I understand what that does

I also understand how Index and Match work ...and had been using COUNTIF for counting numbers of cell that meets my criteria
yet ....... I cannot understand how the COUNTIF work here and how it leads to the result in the F column

I believe the purpose of the formula is to filter out all the duplicated items on column D but how? Please help.

Capture.JPG

2. ## Re: Can you explain how this formula works?

Hi,

The Countif sets up an array of 1s or 0s for where the values in all the cells above the current formula exist in the whole of column D. Where ther is a first occurrence of the column d cell there will always be a zero in the array and hence the MATCH with zero will return the row number in column D where that value is found an then Index uses that row number.

Where there is a duplicate the countif array will contain all 1s, and hence a Match for the value 0 will return an error and hence a blank

However since you have 365 a better formula would be

Formula:
`Please Login or Register  to view this content.`
which will then Spill the fomula automatically as far as required to list the unique values.

3. ## Re: Can you explain how this formula works?

I do not know why, but I do not have the UNIQUE formula
I used to have XLOOKUP before I switched computer

I think it is the same reason I am not getting UNIQUE; any idea?

I bet my struggle is I do not understand how we can getting the 1s and the 0s

I thought the COUNTIF formula works as =COUNTIF (range, criteria)

in the above formula, range we are trying to look at is \$F\$1:F1, which will increase to \$F\$1:F2 as we move downward
However, the criteria is not "text" or number .... but \$D\$2:\$D\$91 ... how does it automatically return the value (which is text from the D column) ...
or what my struggle is to understand, why it F3 result is "New Sale/Account" instead of "Package Changes*"? they both appeared only once between F1 and F3

4. ## Re: Can you explain how this formula works?

Stay in F3
COUNTIF(\$F\$1:F2,\$D\$2:\$D\$91)

how to set up an array of 1s or 0s

COUNTIF(\$F\$1:F2,D2)=1
COUNTIF(\$F\$1:F2,D3)=0
COUNTIF(\$F\$1:F2,D4)=0
....
COUNTIF(\$F\$1:F2,D91)=0

yield an array {1;0;0;0;...} where first "0" indicates 1st position (D3) that not exist in F1:F2
then MATCH(...) returns 2, with this INDEX give "New sale/Account"

5. ## Re: Can you explain how this formula works?

Originally Posted by Richard Buttrey
. . . The Countif sets up an array of 1s or 0s . . .
Picky: it's produces and array of 0s or positive values. Note that Technical Support appears twice in col D, so in the formula in col F in the row just below Technical Support in that column, COUNTIF would give 2 (or more) as the value in the 6th and 9th entries the array it returns. The 0s are important. The positive values are irrelevant other than for not being 0s.

6. Originally Posted by bebo021999
Stay in F3
COUNTIF(\$F\$1:F2,\$D\$2:\$D\$91)

how to set up an array of 1s or 0s

COUNTIF(\$F\$1:F2,D2)=1
COUNTIF(\$F\$1:F2,D3)=0
COUNTIF(\$F\$1:F2,D4)=0
....
COUNTIF(\$F\$1:F2,D91)=0

yield an array {1;0;0;0;...} where first "0" indicates 1st position (D3) that not exist in F1:F2
then MATCH(...) returns 2, with this INDEX give "New sale/Account"
Oh mine ... You solved the mystery.
Well my mystery .... I made it sound like something not everyone know but might just be me stupid question.

Thank you both of you for explaining it to me.

7. ## Re: Can you explain how this formula works?

Originally Posted by mpun

I do not know why, but I do not have the UNIQUE formula
I used to have XLOOKUP before I switched computer

I think it is the same reason I am not getting UNIQUE; any idea?

I bet my struggle is I do not understand how we can getting the 1s and the 0s

I thought the COUNTIF formula works as =COUNTIF (range, criteria)

in the above formula, range we are trying to look at is \$F\$1:F1, which will increase to \$F\$1:F2 as we move downward
However, the criteria is not "text" or number .... but \$D\$2:\$D\$91 ... how does it automatically return the value (which is text from the D column) ...
or what my struggle is to understand, why it F3 result is "New Sale/Account" instead of "Package Changes*"? they both appeared only once between F1 and F3
Turns out my office 365 didn't update to newest version automatically like it should.

Finally get my xlookup back so I think I can get the unique formula now

THanks for suggesting that Richard

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1