Index, Match CountIf Formula

[zone709]

So I use this formula on a sheet I have now which works fine but I would like to use it pulling from another tab on my sheet.

=IFERROR(INDEX(D$2:D$306,MATCH(0,INDEX(COUNTIF(D$1:D1,D$2:D$306),0),0)),"")

Basically I want to pull the D$2:D$306 from a tab called Detailed_View.

Not sure how i would fit Detailed_View in after =IFERROR(INDEX & after the Countif

Thanks

 

[Toadstool]

Hi Zone709,

If all the column D references should be to Detailed_View then

=IFERROR(INDEX(Detailed_View!$D$2:$D$306,MATCH(0,INDEX(COUNTIF(Detailed_View!D$1:D1,Detailed_View!$D$2:$D$306),0),0)),"")

 

[zone709]

Thanks for reply. Its actually looking at the D column on Detailed_view towards the D column on current sheet.

It works kind of but when i drag the formula downward. Its pick up the first number once, but after that it picks up 5 of the same numbers. Maybe something needs to be altered. It should pick up next different number then next different number after that. The original picks up the number once even if there duplicate in the row.

 

[Toadstool]

I think this is what you're trying for:

Zone709.xlsx
	D
1	Name
2	Tom
3	Sally
4	Bert
5	Jim
6	Tom
7	Keith
8	Bert
9	Jim
10	Tom
11	Sally
12	Bert
13	Jim
14
Detailed_View

Zone709.xlsx
	D
1	Uname
2	Tom
3	Sally
4	Bert
5	Jim
6	Keith
7	0
8
9
Sheet2

Cell Formulas
Range	Formula
D2:D9	D2	=IFERROR(INDEX(Detailed_View!$D$2:$D$306,MATCH(0,INDEX(COUNTIF(D$1:D1,Detailed_View!$D$2:$D$306),0),0)),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[zone709]

I am on Version 2016. Ok it works i just need one small change. It will always starts on Detail_View D2, but where i am putting the formula on other tab sheet. I need to start at D11 having the same results as it is now.. I change D1 to D11 but that didn't work.

 

[Fluff]

I am on Version 2016.

Please don't forget to update you account details.
This should work

=IFERROR(INDEX(Detailed_View!$D$2:$D$306,MATCH(0,INDEX(COUNTIF(D$11:D11,Detailed_View!$D$2:$D$306),0),0)),"")

Entered using Ctrl Shift Enter

 

[zone709]

Hi thanks for reply. It returns 0 not sure why

 

[Fluff]

You still haven't updated your account profile. Why not do it now, lest you forget.

 

[Toadstool]

Maybe..

Zone709.xlsx
	D
10	Uname
11	Tom
12	Sally
13	Bert
14	Jim
15	Keith
16	0
17
18
StartD11

Cell Formulas
Range	Formula
D11:D18	D11	=IFERROR(INDEX(Detailed_View!$D$2:$D$306,MATCH(0,INDEX(COUNTIF(D$10:D10,Detailed_View!$D$2:$D$306),0),0)),"")
Press CTRL+SHIFT+ENTER to enter array formulas.