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.
Thanks in advance.
.
Biff
Microsoft MVP - Excel
Don't be afraid to use volatile functions or array formulas
Tell us what version of Excel you're using
KISS - Keep It Simple Stupid
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.
.
Biff
Microsoft MVP - Excel
Don't be afraid to use volatile functions or array formulas
Tell us what version of Excel you're using
KISS - Keep It Simple Stupid
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.
Assuming too much and qualifying too much are two faces of the same problem.
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
Spreadsheet Formulas
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.
{=MIN(IF($G$2:$G$1000=AD46;IF($U$2:$U$1000<4;ROW($G$2:$G$1000))))}
Yes, I pressed CTRL + SHIFT + ENTER to enter it and I didn't type the { and the } in the code.
Here's another CSE formula that returns the row number.
=MATCH(E2, REPT($B$1:$B$700, ($C$1:$C$700<4)), 0)
Why is D = 9?
You said:
So, if D = 9 then you actually want the value that is less than or equal to 4.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.
In that case simply change <4 to <=4.
Still array entered**:
=MIN(IF($G$2:$G$1000=AD46;IF($U$2:$U$1000<=4;ROW($G$2:$G$1000))))
** 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.
.
Biff
Microsoft MVP - Excel
Don't be afraid to use volatile functions or array formulas
Tell us what version of Excel you're using
KISS - Keep It Simple Stupid
Thanks this works perfectly.
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
.
Biff
Microsoft MVP - Excel
Don't be afraid to use volatile functions or array formulas
Tell us what version of Excel you're using
KISS - Keep It Simple Stupid
Like this thread? Share it with others