Index, Match CountIf Formula

zone709

Well-known Member
Joined
Mar 1, 2016
Messages
1,976
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,449
Office Version
  1. 2016
Platform
  1. Windows
Hi Zone709,

If all the column D references should be to Detailed_View then
Excel Formula:
=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

Well-known Member
Joined
Mar 1, 2016
Messages
1,976
Office Version
  1. 365
Platform
  1. Windows
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.
 
Last edited:

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,449
Office Version
  1. 2016
Platform
  1. Windows
I think this is what you're trying for:

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


Zone709.xlsx
D
1Uname
2Tom
3Sally
4Bert
5Jim
6Keith
70
8 
9 
Sheet2
Cell Formulas
RangeFormula
D2:D9D2=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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,614
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Mar 1, 2016
Messages
1,976
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,614
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

I am on Version 2016.
Please don't forget to update you account details. ;)
This should work
Excel Formula:
=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

Well-known Member
Joined
Mar 1, 2016
Messages
1,976
Office Version
  1. 365
Platform
  1. Windows
Hi thanks for reply. It returns 0 not sure why
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,614
Office Version
  1. 365
Platform
  1. Windows
You still haven't updated your account profile. Why not do it now, lest you forget. ;)
 

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,449
Office Version
  1. 2016
Platform
  1. Windows
Maybe..

Zone709.xlsx
D
10Uname
11Tom
12Sally
13Bert
14Jim
15Keith
160
17 
18 
StartD11
Cell Formulas
RangeFormula
D11:D18D11=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.
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,129,685
Messages
5,637,808
Members
416,983
Latest member
LessThanAverageUser

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top