How to find Value using 2 rows as a reference

mike&jess

New Member
Joined
Aug 3, 2012
Messages
7
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

Pressure0.010.020.030.04
kvs dn
31503.03.74.45.0
40504.04.95.86.4
49654.85.97.07.6
63656.47.79.010.0
78807.69.110.512.0
1008010.012.515.016.5

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

Some videos you may like

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
Joined
Nov 17, 2011
Messages
1,952
Office Version
365
Platform
Windows
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
Joined
Jan 9, 2008
Messages
14,841
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
Joined
Jul 15, 2012
Messages
1,953
why did you say 2 rows ? for each kvs ther'is only one row
 
Last edited:

Mindpsyche

Well-known Member
Joined
Mar 19, 2012
Messages
760
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
21Answer0.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
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})

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>

Array Formulas
CellFormula
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

New Member
Joined
Aug 3, 2012
Messages
7
Thanks for all the replies, Mindpsyche's solution worked perfectly.

Thanks again!
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,178
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...
 

Watch MrExcel Video

Forum statistics

Threads
1,099,699
Messages
5,470,250
Members
406,686
Latest member
BNR_ 1980

This Week's Hot Topics

Top