Matching on multiple criteria

[Vikram J B]

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

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

<tbody>
</tbody>

 

[sheetspread]

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

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]

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

 

[sheetspread]

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]

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]

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]

Here's the code for multiple rows:

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]

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

 

[Vikram J B]

hi,

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

much thanks