# How to find Value using 2 rows as a reference

#### mike&jess

##### New Member
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>

### Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

#### BenMiller

##### Board RegularThe ONLY cool kid on the block
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

##### MrExcel MVP
Based on your Table , try this UDF:-
Code:
``````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

Last edited:

#### patel45

##### Well-known Member
why did you say 2 rows ? for each kvs ther'is only one row

Last edited:

#### Mindpsyche

##### Well-known Member
Not sure if I really understood your explanation but here goes:

Excel 2007
ABCDEFGHIJK
1Pressure Table
20.010.020.030.04Row number
3kvsdn
431503.03.74.45.01
540504.04.95.86.42
649654.85.97.07.63
763656.47.79.010.04
878807.69.110.512.05
91008010.012.515.016.56
10
11User Input Value
12kvs49
13dn65
14Pressure3.0
15
16
17Calculation
18Match Row3To get the rows with the same kvs and dn
19Min 4.8To extract the lowest number in this row, in case the user inputs a number smaller than this.
20Lookup Value4.8As 3 < 4.8 , the lookup value used is 4.8

</tbody>
Sheet1

Worksheet Formulas
CellFormula
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})

</tbody>

<tbody>
</tbody>

Array Formulas
CellFormula
B18{=MATCH(1,(A4:A9=B12)*(B4:B9=B13),0)}

</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

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

Thanks again!

#### Mindpsyche

##### Well-known Member
Hey, thanks for the feedback.

##### MrExcel MVP
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...