How to find Value using 2 rows as a reference

[mike&jess]

We're trying to find a pipe size in a row, that corresponds to a value of flow within a table (which is found by matching an input flow with a table), see example below. For example, a value for kvs and DN are input by user, the user then inputs desired flow rate which needs to match approximately the closest value in the table. This information is used to find the pressure by selecting the appropriate pressure value where kvs AND dn AND Flow rate match. So, for example, if the user selects kvs = 31, dn = 50, and flow rate = 4.3. Excel automatically selects pressure = 0.03 (4.3 is rounded up to 4.4). I know it's a bit complicated but we need help! Thanks

	Pressure	0.01	0.02	0.03	0.04
kvs	dn
31	50	3.0	3.7	4.4	5.0
40	50	4.0	4.9	5.8	6.4
49	65	4.8	5.9	7.0	7.6
63	65	6.4	7.7	9.0	10.0
78	80	7.6	9.1	10.5	12.0
100	80	10.0	12.5	15.0	16.5

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

 

[BenMiller]

When you say "closest" - is that always rounding up (e.g. 4.3 to 4.4)? Or is it really the closest (so if I enter 4.3 I'd get 4.2 and not 4.5)?

 

[MickG]

Based on your Table , try this UDF:-

Function sPipe(Kvs, Fr)
Dim Rng As Range, Dn As Range
Dim Ac As Integer
Set Rng = Range(Range("A1"), Range("A" & Rows.count).End(xlUp))
For Each Dn In Rng
    If Dn = Kvs Then
        For Ac = 3 To 6
            If Dn(, Ac) > Fr Then sPipe = Dn(, Ac): Exit For
        Next Ac
    End If
Next Dn
End Function

NB:- if you want Pressure returned , Changes sPipe = Dn(, Ac) for sPipe = Rng(1, Ac)

Regards Mick

 

[patel45]

why did you say 2 rows ? for each kvs ther'is only one row

 

[Mindpsyche]

Not sure if I really understood your explanation but here goes:


Excel 2007

	A	B	C	D	E	F	G	H	I	J	K
1			Pressure Table
2			0.01	0.02	0.03	0.04		Row number
3	kvs	dn
4	31	50	3.0	3.7	4.4	5.0		1
5	40	50	4.0	4.9	5.8	6.4		2
6	49	65	4.8	5.9	7.0	7.6		3
7	63	65	6.4	7.7	9.0	10.0		4
8	78	80	7.6	9.1	10.5	12.0		5
9	100	80	10.0	12.5	15.0	16.5		6
10
11	User Input Value
12	kvs	49
13	dn	65
14	Pressure	3.0
15
16
17	Calculation
18	Match Row	3	To get the rows with the same kvs and dn
19	Min	4.8	To extract the lowest number in this row, in case the user inputs a number smaller than this.
20	Lookup Value	4.8	As 3 < 4.8 , the lookup value used is 4.8
21	Answer	0.01

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula B19 =MIN(OFFSET($C$4,$B$18-1,):OFFSET($F$4,$B$18-1,,)) B20 =IF(B14<B19,B19,B14) B21 =LOOKUP(B20,OFFSET($C$4,$B$18-1,):OFFSET($F$4,$B$18-1,,),{0.01,0.02,0.03,0.04}) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula B18 {=MATCH(1,(A4:A9=B12)*(B4:B9=B13),0)} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

 

[mike&jess]

Thanks for all the replies, Mindpsyche's solution worked perfectly.

Thanks again!

 

[Mindpsyche]

Hey, thanks for the feedback.

 

[Aladin Akyurek]

We're trying to find a pipe size in a row, that corresponds to a value of flow within a table (which is found by matching an input flow with a table), see example below. For example, a value for kvs and DN are input by user, the user then inputs desired flow rate which needs to match approximately the closest value in the table. This information is used to find the pressure by selecting the appropriate pressure value where kvs AND dn AND Flow rate match. So, for example, if the user selects kvs = 31, dn = 50, and flow rate = 4.3. Excel automatically selects pressure = 0.03 (4.3 is rounded up to 4.4). I know it's a bit complicated but we need help! Thanks

	Pressure	0.01	0.02	0.03	0.04
kvs	dn
31	50	3.0	3.7	4.4	5.0
40	50	4.0	4.9	5.8	6.4
49	65	4.8	5.9	7.0	7.6
63	65	6.4	7.7	9.0	10.0
78	80	7.6	9.1	10.5	12.0
100	80	10.0	12.5	15.0	16.5

<tbody>
</tbody>

Does dn matter at all? A kvs value seems to be sufficient for finding the row to look at...