CODEWITHSUNDEEP
excel
Justin
Justin

Reputation: 87

Give the row number that contains a certain number in a specific column

I have a table in excel with the following data:

      A       B      C
1    Name    Age    Fare
2    Owen    22     7.25
3    Lionel  36     0
4    John    38     71.3
5    Gladys  5      8
6    Mark    12     0

My problem is I want to locate the row numbers where the value for column C is zero (in my example, it will give the 3rd and 6th row). I'll appreciate any kind of help since the data I got has 1000 rows and locating the row number with a zero value for column C is a bit time-consuming. Thank you!

Upvotes: 0

Views: 214

Answers (3)

QHarr
QHarr

Reputation: 84465

Select column D adjacent to column C used range and enter the formula in one cell, with all still highlighted,

=IF(C2:C6=0,ROW())

C2:C6 being the used range in this case, and enter as an array formula with Ctrl+Shift+Enter.

You can then filter the results and remove FALSE to leave the row numbers.

Row number

Upvotes: 0

Gary's Student
Gary's Student

Reputation: 96753

try the following:

=MATCH(0,C:C,0)

enter image description here

EDIT#1:

Place the above formula in E1 and in E2 enter:

=MATCH(0,INDEX(C:C,E1+1):INDEX(C:C,9999),0)+E1

and copy down ward until you see errors.

enter image description here

To avoid the appearance of errors use:

=IFERROR(MATCH(0,INDEX(C:C,E1+1):INDEX(C:C,9999),0)+E1,"")

instead.

Upvotes: 1

Harun24hr
Harun24hr

Reputation: 36840

Use following formula in D2 cell then drag and down as needed

=IF(C2=0,ROW(),"")

enter image description here

Upvotes: 3

Related Questions