# Number of the row with the first occurance

This is a discussion on Number of the row with the first occurance within the Excel Questions forums, part of the Question Forums category; Hi. I need to know the number of the row where 2 different columns specify to specific criteria. Here's an ...

1. ## Number of the row with the first occurance

Hi.

I need to know the number of the row where 2 different columns specify to specific criteria. Here's an example of my sheet. It is not from the real sheet, but it represents what kind of formula I need.

 A B C D E F 1 Name Class Year Class Row 2 John A 1 A 3 James A 5 B 4 Jack B 2 C 5 Jones C 1 6 Jimmy A 2 7 Mark C 6

In F2 I need to have the number of first the row where B2:B7 matches E2 and where C2:C7 is lower then 4. For F3 it's the same except that it has to match E3 and so on. The value has to be lower than 4 and a 0 can also be a value in this list.

2. ## Re: Number of the row with the first occurance

Originally Posted by Ama-Chan
Hi.

I need to know the number of the row where 2 different columns specify to specific criteria. Here's an example of my sheet. It is not from the real sheet, but it represents what kind of formula I need.

 A B C D E F 1 Name Class Year Class Row 2 John A 1 A 3 James A 5 B 4 Jack B 2 C 5 Jones C 1 6 Jimmy A 2 7 Mark C 6

In F2 I need to have the number of first the row where B2:B7 matches E2 and where C2:C7 is lower then 4. For F3 it's the same except that it has to match E3 and so on. The value has to be lower than 4 and a 0 can also be a value in this list.

Do you want the row number or do you really want the name from column A?

A = John
B = Jack
C = Jones

3. ## Re: Number of the row with the first occurance

I want the row number.

4. ## Re: Number of the row with the first occurance

Originally Posted by Ama-Chan
I want the row number.
Try this...

Sheet1

 A B C D E F 2 John A 1 _ A 2 3 James A 5 _ B 4 4 Jack B 2 _ C 5 5 Jones C 1 _ _ _ 6 Jimmy A 2 _ _ _ 7 Mark C 6 _ _ _

This array formula** entered in F2 and copied down:

=MIN(IF(B\$2:B\$7=E2,IF(C\$2:C\$7<4,ROW(C\$2:C\$7))))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

5. ## Re: Number of the row with the first occurance

Originally Posted by Ama-Chan
Hi.

I need to know the number of the row where 2 different columns specify to specific criteria. Here's an example of my sheet. It is not from the real sheet, but it represents what kind of formula I need.

 A B C D E F 1 Name Class Year Class Row 2 John A 1 A 3 James A 5 B 4 Jack B 2 C 5 Jones C 1 6 Jimmy A 2 7 Mark C 6

In F2 I need to have the number of first the row where B2:B7 matches E2 and where C2:C7 is lower then 4. For F3 it's the same except that it has to match E3 and so on. The value has to be lower than 4 and a 0 can also be a value in this list.

Native row number corresponding to the first matching value...

F2, control+shift+enter, not just enter, and copy down

=MIN(IF(\$B\$2:\$B\$7=E2,IF(\$C\$2:\$C\$7 < 4, ROW(\$B\$2:\$B\$7))))

If you want to return the first corresponding name...

=INDEX(\$A\$2:\$A\$7,MIN(IF(\$B\$2:\$B\$7=E2,IF(\$C\$2:\$C\$7 < 4, ROW(\$B\$2:\$B\$7)))))

If you want some control, these formulas can be wrapped into a CountIf expression...

=IF(COUNTIF(\$B\$2:\$B\$7,E2),MIN(IF(\$B\$2:\$B\$7=E2,IF(\$C\$2:\$C\$7 < 4, ROW(\$B\$2:\$B\$7)))),"")

Use the same set up for the second formula.

_________________
Posted from Istanbul.

6. ## Re: Number of the row with the first occurance

In the text example it worked fine, but in the real sheet it produces some errors.
I have extended the text sheet for a moment and with the formula you gave me this is the result.

 A B C D E F 1 Name Class Year Class Row 2 John A 1 A 2 3 James A 5 B 4 4 Jack B 2 C 6 5 Abraham C 4 D 0 6 Jones C 1 7 Jimmy A 2 8 Mark C 6 9 Daniel D 4

 Cell Formula F2 {=MIN(IF(B\$2:B\$9=E2;IF(C\$2:C\$9<4;ROW(C\$2:C\$9))))} F3 {=MIN(IF(B\$2:B\$9=E3;IF(C\$2:C\$9<4;ROW(C\$2:C\$9))))} F4 {=MIN(IF(B\$2:B\$9=E4;IF(C\$2:C\$9<4;ROW(C\$2:C\$9))))} F5 {=MIN(IF(B\$2:B\$9=E5;IF(C\$2:C\$9<4;ROW(C\$2:C\$9))))}
Formula Array:
Produce enclosing
{ } by entering

Now this is exactly what I want, but in the real sheet that I'm working on this is what happens.

 A B C D E F 1 Name Class Year Class Row 2 John A 1 A 0 3 James A 5 B 4 4 Jack B 2 C 5 5 Abraham C 4 D 9 6 Jones C 1 7 Jimmy A 2 8 Mark C 6 9 Daniel D 4

This is the formula I used in the real sheet and I don't think I did something wrong with it.

Yes, I pressed CTRL + SHIFT + ENTER to enter it and I didn't type the { and the } in the code.

7. ## Re: Number of the row with the first occurance

Here's another CSE formula that returns the row number.
=MATCH(E2, REPT(\$B\$1:\$B\$700, (\$C\$1:\$C\$700<4)), 0)

8. ## Re: Number of the row with the first occurance

Originally Posted by Ama-Chan
In the text example it worked fine, but in the real sheet it produces some errors.
I have extended the text sheet for a moment and with the formula you gave me this is the result.

 A B C D E F 1 Name Class Year Class Row 2 John A 1 A 2 3 James A 5 B 4 4 Jack B 2 C 6 5 Abraham C 4 D 0 6 Jones C 1 7 Jimmy A 2 8 Mark C 6 9 Daniel D 4

 Cell Formula F2 {=MIN(IF(B\$2:B\$9=E2;IF(C\$2:C\$9<4;ROW(C\$2:C\$9))))} F3 {=MIN(IF(B\$2:B\$9=E3;IF(C\$2:C\$9<4;ROW(C\$2:C\$9))))} F4 {=MIN(IF(B\$2:B\$9=E4;IF(C\$2:C\$9<4;ROW(C\$2:C\$9))))} F5 {=MIN(IF(B\$2:B\$9=E5;IF(C\$2:C\$9<4;ROW(C\$2:C\$9))))}
Formula Array:
Produce enclosing { } by entering

Now this is exactly what I want, but in the real sheet that I'm working on this is what happens.

 A B C D E F 1 Name Class Year Class Row 2 John A 1 A 0 3 James A 5 B 4 4 Jack B 2 C 5 5 Abraham C 4 D 9 6 Jones C 1 7 Jimmy A 2 8 Mark C 6 9 Daniel D 4

This is the formula I used in the real sheet and I don't think I did something wrong with it.

Yes, I pressed CTRL + SHIFT + ENTER to enter it and I didn't type the { and the } in the code.
Why is D = 9?

You said:

In F2 I need to have the number of first the row where B2:B7 matches E2 and where C2:C7 is lower then 4.
So, if D = 9 then you actually want the value that is less than or equal to 4.

In that case simply change <4 to <=4.

Still array entered**:

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

9. ## Re: Number of the row with the first occurance

Originally Posted by mikerickson
Here's another CSE formula that returns the row number.
=MATCH(E2, REPT(\$B\$1:\$B\$700, (\$C\$1:\$C\$700<4)), 0)
Thanks this works perfectly.

Originally Posted by T. Valko
So, if D = 9 then you actually want the value that is less than or equal to 4.
No that is not what I wanted that is what Excel made of it. The first sheet of what I posted in post #6 is what I actually wanted and the second is what Excel made of it.

10. ## Re: Number of the row with the first occurance

Originally Posted by Ama-Chan
No that is not what I wanted that is what Excel made of it. The first sheet of what I posted in post #6 is what I actually wanted and the second is what Excel made of it.
Here's a small sample file that demonstrates this.

zzzAma-Chan.xls 16kb

http://cjoint.com/?AJtpGgGeiLG

As you'll see the formula does return the correct results. If in your file you do not get the correct results then there must be some problem with your data.

The article at this link describes some common data problems that can cause problems.

http://contextures.com/xlFunctions02.html#Trouble