First row to match multiple criteria

[berndeijg]

Dear all,

I have to find the row number of the first row matching multiple criteria.

Sheet2A:A=Sheet1!$B$1
Sheet2B:B>=Sheet1!$B$2
Sheet2C:C>=Sheet1!$B$3
Sheet2D:D>=Sheet1!$B$4

I can find lots of information about MATCH-INDEX functions, but it is always about looking up a single value.

Any help solving this challenge or sending me to the right thread will be much appreciated.

Bernd

 

[Robertvk]

It's not really clear to me what you're trying to achieve. In the example above you compare rows to cells? It would seem to me that would always result in a FALSE.

 

[Sebss]

Dear all,

I have to find the row number of the first row matching multiple criteria.

Sheet2A:A=Sheet1!$B$1
Sheet2B:B>=Sheet1!$B$2
Sheet2C:C>=Sheet1!$B$3
Sheet2D:D>=Sheet1!$B$4

I can find lots of information about MATCH-INDEX functions, but it is always about looking up a single value.

Any help solving this challenge or sending me to the right thread will be much appreciated.

Bernd

Could you explain what it is you are trying to do?

 

[berndeijg]

Hello Robertvk and sebss,

I got the following data.

Crane	Max. Load	Max. Outreach	Max. Liftheight	x
A	70	38,3	43,5	8541
A	60	40,1	42,2	452
A	100	32,82	48	4525
A	90	35,55	45,8	867
A	80	37,8	43,6	4586
B	70	38,3	43,5	278
B	60	40,1	42,2	4564
B	100	32,82	48	2782
B	90	35,55	45,8	45
B	80	37,8	43,6	28525

<colgroup><col span="4"><col></colgroup><tbody>
</tbody>

And in a second sheet the user can enter the following data.

Crane	B
Load to lift	75
Outreach	35
Liftheight	15

<colgroup><col><col></colgroup><tbody>
</tbody>

I want a formula that returns the data in the row that first meets all the criteria
Crane is equal to B
Max. Load is more than 75
Max. Outreach is more than 35
Max. Liftheight is more than 15

In this example

B

90

35,55

45,8

45

<tbody>
</tbody>

I hope this example makes my question clear.

Bernd

 

[Robertvk]

Ah got it. I'm not sure if you could achieve this with regular functions. It is possible by using VBA or perhaps by creating a UDF trough vba. Unless someone else has a better idea I would suggest going that route.

 

[berndeijg]

Hello Robertvk and Sebss,

I think I found a sollution that works for me!

Control+shift+enter:

=INDEX(Sheet2!A:A;MATCH(1;IF(Sheet1!B1=Sheet2!A:A;IF(Sheet1!B2<=Sheet2!B:B;IF(Sheet1!B3<=Sheet2!C:C;IF(Sheet1!B4<=Sheet2!D:D;1))));0))

Returns the data of column A of the first row that matches all the criteria.

Tenx for looking in to it!

Bernd

 

[Robertvk]

Oh great! Good job for posting your solution. Good luck.

 

[Aladin Akyurek]

Sheet1

Row\Col	A​	B​	C​	D​	E​
1​	Crane	Max. Load	Max. Outreach	Max. Liftheight	x
2​	A	70​	38.3​	43.5​	8541​
3​	A	60​	40.1​	42.2​	452​
4​	A	100​	32.82​	48​	4525​
5​	A	90​	35.55​	45.8​	867​
6​	A	80​	37.8​	43.6​	4586​
7​	B	70​	38.3​	43.5​	278​
8​	B	60​	40.1​	42.2​	4564​
9​	B	100​	32.82​	48​	2782​
10​	B	90​	35.55​	45.8​	45​
11​	B	80​	37.8​	43.6​	28525​

Sheet2

Row\Col	A​	B​	C​	D​	E​
1​	Crane	B
2​	Load to lift	75​
3​	Outreach	35​
4​	Liftheight	15​
5​	2​
6​
7​	Idx	Max. Load	Max. Outreach	Max. Liftheight	x
8​	9​	90​	35.55​	45.8​	45​
9​	10​	80​	37.8​	43.6​	28525​
10​

In A5 just enter:

=COUNTIFS(Sheet1!A2:A11,B1,Sheet1!B2:B11,">="&B2,Sheet1!C2:C11,">="&B3,
    Sheet1!D2:D11,">="&B4)<strike></strike>

In A8 control+shift+enter, not just enter, and copy down:

=IF(ROWS($A$8:A8)>$A$5,"",SMALL(IF(Sheet1!$A$2:$A$11=$B$1,IF(Sheet1!$B$2:$B$11>=$B$2,
    IF(Sheet1!$C$2:$C$11>=$B$3,IF(Sheet1!$D$2:$D$11>=$B$4,
    ROW(Sheet1!$A$2:$A$11)-ROW(Sheet1!$A$2)+1)))),ROWS($A$8:A8)))

In B8 just enter, copy across, and down:

=IF($A8="","",INDEX(Sheet1!$A$2:$E$11,$A8,MATCH(B$7,Sheet1!$A$1:$E$1,0)))

<strike></strike>

 

[berndeijg]

Dear all,

I have run into a follow-up question. If there is no match the function returns #N/A, I would like it to return a 0 or FALSE, is there a possibility to do so?

Bernd

 

[Aladin Akyurek]

Dear all,

I have run into a follow-up question. If there is no match the function returns #N/A, I would like it to return a 0 or FALSE, is there a possibility to do so?

Bernd

See post #8 for an appropriate way of extracting a multiconditional sublist.