Matching on multiple criteria

Vikram J B · Jul 1, 2018

The Input Values to be entered ..IN F2 & F4. The Value in F2 needs to be searched in the array G14:K19 AND the value Entered in F4 needs to be searched in the array L14:P19. If both the values are found on the same Row and corresponding columns , We need the corresponding Model to be returned. There is an instance of F2 in K12 of the Head Range array but the corresponding cell to it in the Corresponding Discharge Value Array is P12 is not equal to F4 and hence needs to be ignored. Only the cells matching F2 head range array and F4 Matching cells lying in the same column and row no as F2 in the head Range should return the value of the model in column E

[TABLE="class: cms_table, width: 979"]
<tbody>[TR]
[TD][/TD]
[TD="colspan: 4"]Criteria to Select Pumpsets[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]Input Head due to all causes[/TD]
[TD]110[/TD]
[TD]in Mtrs[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]Input Required Discharge[/TD]
[TD]400[/TD]
[TD]in LPM[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 5"]Head Range[/TD]
[TD="colspan: 5"]Discharge Range[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]SERIES[/TD]
[TD]MODEL[/TD]
[TD]STG[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]RE68[/TD]
[TD]RE68/03+ET075[/TD]
[TD]3[/TD]
[TD]62[/TD]
[TD]60[/TD]
[TD]57[/TD]
[TD]53[/TD]
[TD]47[/TD]
[TD]0[/TD]
[TD]210[/TD]
[TD]300[/TD]
[TD]390[/TD]
[TD]480[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]RE68[/TD]
[TD]RE68/04+ET093[/TD]
[TD]4[/TD]
[TD]82[/TD]
[TD]80[/TD]
[TD]76[/TD]
[TD]70[/TD]
[TD]62[/TD]
[TD]0[/TD]
[TD]210[/TD]
[TD]300[/TD]
[TD]390[/TD]
[TD]480[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]RE65[/TD]
[TD]RE65/05 + ET093[/TD]
[TD]5[/TD]
[TD]98[/TD]
[TD]97[/TD]
[TD]96[/TD]
[TD]94[/TD]
[TD]91[/TD]
[TD]0[/TD]
[TD]150[/TD]
[TD]200[/TD]
[TD]250[/TD]
[TD]300[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]RE65[/TD]
[TD]RE65/06 + ET112[/TD]
[TD]6[/TD]
[TD]118[/TD]
[TD]116[/TD]
[TD]114[/TD]
[TD]113[/TD]
[TD]110[/TD]
[TD]0[/TD]
[TD]150[/TD]
[TD]200[/TD]
[TD]250[/TD]
[TD]300[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]RE70[/TD]
[TD]RE70/06 + ET150[/TD]
[TD]6[/TD]
[TD]114[/TD]
[TD]111[/TD]
[TD]110[/TD]
[TD]109[/TD]
[TD]103[/TD]
[TD]0[/TD]
[TD]300[/TD]
[TD]400[/TD]
[TD]500[/TD]
[TD]600[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]RE70[/TD]
[TD]RE70/07 + ET187[/TD]
[TD]7[/TD]
[TD]133[/TD]
[TD]129[/TD]
[TD]128[/TD]
[TD]127[/TD]
[TD]120[/TD]
[TD]0[/TD]
[TD]300[/TD]
[TD]400[/TD]
[TD]500[/TD]
[TD]600[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

sheetspread · Jul 2, 2018

This?

Excel 2010

B

C

D

E

F

G

H

I

J

K

L

M

N

1

Criteria to Select Pumpsets

2

Input Head due to all causes

110

in Mtrs

RE70/06 + ET150

3

4

Input Required Discharge

400

in LPM

5

6

7

Head Range

Discharge Range

8

SERIES

MODEL

STG

1

2

3

4

5

1

2

3

4

5

9

RE68

RE68/03+ET075

3

62

60

57

53

47

0

210

300

390

480

10

RE68

RE68/04+ET093

4

82

80

76

70

62

0

210

300

390

480

11

RE65

RE65/05 + ET093

5

98

97

96

94

91

0

150

200

250

300

12

RE65

RE65/06 + ET112

6

118

116

114

113

110

0

150

200

250

300

13

RE70

RE70/06 + ET150

6

114

111

110

109

103

0

300

400

500

600

14

RE70

RE70/07 + ET187

7

133

129

128

127

120

0

300

400

500

600

Sheet1

Code:

Sub findcell()
Dim c As Range
For Each c In Range("e9:n14")
If c = Range("d2") And c.Offset(0, 5) = Range("d4") Then
Range("g2") = Cells(c.Row, 3)
End If
Next c
End Sub

Vikram J B · Jul 2, 2018

Thank you... But Could be please help to do the same in EXCEL WITH FORMULAS..... PLS

sheetspread · Jul 2, 2018

Excel 2010

B

C

D

E

F

G

H

I

J

K

L

M

N

1

Criteria to Select Pumpsets

2

Input Head due to all causes

110

in Mtrs

RE70/06 + ET150

3

4

Input Required Discharge

400

in LPM

5

6

7

Head Range

Discharge Range

8

SERIES

MODEL

STG

1

2

3

4

5

1

2

3

4

5

9

RE68

RE68/03+ET075

3

62

60

57

53

47

0

210

300

390

480

10

RE68

RE68/04+ET093

4

82

80

76

70

62

0

210

300

390

480

11

RE65

RE65/05 + ET093

5

98

97

96

94

91

0

150

200

250

300

12

RE65

RE65/06 + ET112

6

118

116

114

113

110

0

150

200

250

300

13

RE70

RE70/06 + ET150

6

114

111

110

109

103

0

300

400

500

600

14

RE70

RE70/07 + ET187

7

133

129

128

127

120

0

300

400

500

600

Sheet1

Cell Formulas
Range	Formula
G2	=INDEX($C$9:$C$14,SUMPRODUCT(--($E$9:$I$14=D2)--(OFFSET($E$9:$I$14,0,5)=D4)ROW($E$9:$I$14))-8)

Vikram J B · Jul 2, 2018

Thanks very much .... But what is the -8 at the end of the formula ...

Is it OK to simply copy and past the formula to the excel sheet ? or do i need to do anything else, as i am getting a ref# error.

Incase I need to search for multiple value meeting the 2 input Criterias, then how do i find it from 10000+ records like this.

Thanks in advance ...

sheetspread · Jul 2, 2018

The header above is in row 8, otherwise subtract a different number (be sure the columns line up also to avoid error messages). I wrote a macro first in case you have multiple values. Formulas won't work well with 10000+ records unless you're willing to put the data in a normal (1 to 1) structure.

sheetspread · Jul 2, 2018

Here's the code for multiple rows:

Code:

Sub findcell()
Dim c As Range, x%
x = 0
For Each c In Range("e9:n14")
If c = Range("d2") And c.Offset(0, 5) = Range("d4") Then
Range("p2").Offset(x) = Cells(c.Row, 3)
x = x + 1
Else
End If
Next c
End Sub

If you want to try a formula, something like:

Excel 2010

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

1

Criteria to Select Pumpsets

RE68/04+ET093

2

Input Head due to all causes

110

in Mtrs

RE70/06 + ET150

3

4

Input Required Discharge

400

in LPM

5

6

7

Head Range

Discharge Range

8

SERIES

MODEL

STG

1

2

3

4

5

1

2

3

4

5

9

RE68

RE68/03+ET075

3

62

60

57

53

47

0

210

300

390

480

10

RE68

RE68/04+ET093

4

82

80

76

70

110

0

210

300

390

400

11

RE65

RE65/05 + ET093

5

98

97

96

94

91

0

150

200

250

300

12

RE65

RE65/06 + ET112

6

118

116

114

113

110

0

150

200

250

300

13

RE70

RE70/06 + ET150

6

114

111

110

109

103

0

300

400

500

600

14

RE70

RE70/07 + ET187

7

133

129

128

127

120

0

300

400

500

600

Sheet1 (3)

Cell Formulas
Range	Formula
P1	{=INDEX($C$9:$C$14,SMALL(IF($E$9:$I$14=$D$2,IF(OFFSET($E$9:$I$14,0,5)=$D$4,ROW($E$9:$I$14))),ROW(A1))-8)}
Press CTRL+SHIFT+ENTER to enter array formulas.

which might be slow

sheetspread · Jul 2, 2018

Your inbox is full so I don't think my response to your message got through

Vikram J B · Jul 3, 2018

hi,

have emptied my inbox.. can u resend ur response ... pls

much thanks

Matching on multiple criteria

Vikram J B

New Member

Excel Facts

sheetspread

Well-known Member

Vikram J B

New Member

sheetspread

Well-known Member

Vikram J B

New Member

sheetspread

Well-known Member

sheetspread

Well-known Member

sheetspread

Well-known Member

Vikram J B

New Member

Similar threads

Forum statistics

Share this page

Matching on multiple criteria

New Member

Excel Facts

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

Well-known Member

Well-known Member

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock