Showing row/cell references from a countif formula

[b19upj]

Hi,

If i perform a countif calculation and the result is 5, how can i find the 5 rows that count formula is referring to so i can then do a vlookup on it and display certain columns from the 5 rows the count found.

I hope that makes sense.

Many thanks

 

[Eric W]

You need to be a bit more specific on your requirements. What you're asking depends highly on how your sheet is laid out. Here's an example of how it could work though:

Excel 2010

	A	B	C	D	E
1	Name	Count		COUNTIF	List
2	A	1		5	A
3	B	2			E
4	C	3			H
5	D	2			J
6	E	1			M
7	F	3
8	G	4
9	H	1
10	I	4
11	J	1
12	K	6
13	L	7
14	M	1
15	N	8
16	O	7
17	P	3

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet8​

Worksheet Formulas Cell Formula D2 =COUNTIF(B2:B17,1) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula E2 {=IF(E1="","",IFERROR(INDEX($A$2:$A$17,SMALL(IF($B$2:$B$17=1,ROW($B$2:$B$17)-ROW($B$2)+1),ROW()-ROW($E$2)+1)),""))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Your sample COUNTIF is in D2. Then you can enter the formula in E2 with Control-Shift-Enter. Then copy it and paste it down the column.

Let me know if you need help adapting it to your sheet.