How to find Value using 2 rows as a reference

M

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

[TABLE="width: 448"]
<tbody>[TR]
[TD][/TD]
[TD]Pressure[/TD]
[TD]0.01[/TD]
[TD]0.02[/TD]
[TD]0.03[/TD]
[TD]0.04[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]kvs [/TD]
[TD] dn[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]31[/TD]
[TD]50[/TD]
[TD]3.0[/TD]
[TD]3.7[/TD]
[TD]4.4[/TD]
[TD]5.0[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]40[/TD]
[TD]50[/TD]
[TD]4.0[/TD]
[TD]4.9[/TD]
[TD]5.8[/TD]
[TD]6.4[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]49[/TD]
[TD]65[/TD]
[TD]4.8[/TD]
[TD]5.9[/TD]
[TD]7.0[/TD]
[TD]7.6[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]63[/TD]
[TD]65[/TD]
[TD]6.4[/TD]
[TD]7.7[/TD]
[TD]9.0[/TD]
[TD]10.0[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]78[/TD]
[TD]80[/TD]
[TD]7.6[/TD]
[TD]9.1[/TD]
[TD]10.5[/TD]
[TD]12.0[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]100[/TD]
[TD]80[/TD]
[TD]10.0[/TD]
[TD]12.5[/TD]
[TD]15.0[/TD]
[TD]16.5[/TD]
[TD="align: right"][/TD]
[/TR]
</tbody><colgroup><col span="2"><col span="4"><col></colgroup>[/TABLE]
 
Sort by date Sort by votes
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)?
 
Upvote 0
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:
Upvote 0
why did you say 2 rows ? for each kvs ther'is only one row
 
Last edited:
Upvote 0
Not sure if I really understood your explanation but here goes:


Excel 2007
ABCDEFGHIJK
Pressure Table
Row number
User Input Value
kvs
dn
Pressure
Calculation
Match RowTo get the rows with the same kvs and dn
Min To extract the lowest number in this row, in case the user inputs a number smaller than this.
Lookup ValueAs 3 < 4.8 , the lookup value used is 4.8

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAC090, align: right"]0.01[/TD]
[TD="bgcolor: #FAC090, align: right"]0.02[/TD]
[TD="bgcolor: #FAC090, align: right"]0.03[/TD]
[TD="bgcolor: #FAC090, align: right"]0.04[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FDE9D9, align: center"]kvs[/TD]
[TD="bgcolor: #93CDDD, align: center"]dn[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FDE9D9, align: center"]31[/TD]
[TD="bgcolor: #93CDDD, align: center"]50[/TD]
[TD="align: center"]3.0[/TD]
[TD="align: center"]3.7[/TD]
[TD="align: center"]4.4[/TD]
[TD="align: center"]5.0[/TD]
[TD="align: right"][/TD]
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #FDE9D9, align: center"]40[/TD]
[TD="bgcolor: #93CDDD, align: center"]50[/TD]
[TD="align: center"]4.0[/TD]
[TD="align: center"]4.9[/TD]
[TD="align: center"]5.8[/TD]
[TD="align: center"]6.4[/TD]
[TD="align: right"][/TD]
[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #FDE9D9, align: center"]49[/TD]
[TD="bgcolor: #93CDDD, align: center"]65[/TD]
[TD="bgcolor: #CCC0DA, align: center"]4.8[/TD]
[TD="bgcolor: #CCC0DA, align: center"]5.9[/TD]
[TD="bgcolor: #CCC0DA, align: center"]7.0[/TD]
[TD="bgcolor: #CCC0DA, align: center"]7.6[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #CCC0DA, align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: #FDE9D9, align: center"]63[/TD]
[TD="bgcolor: #93CDDD, align: center"]65[/TD]
[TD="align: center"]6.4[/TD]
[TD="align: center"]7.7[/TD]
[TD="align: center"]9.0[/TD]
[TD="align: center"]10.0[/TD]
[TD="align: right"][/TD]
[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: #FDE9D9, align: center"]78[/TD]
[TD="bgcolor: #93CDDD, align: center"]80[/TD]
[TD="align: center"]7.6[/TD]
[TD="align: center"]9.1[/TD]
[TD="align: center"]10.5[/TD]
[TD="align: center"]12.0[/TD]
[TD="align: right"][/TD]
[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: #FDE9D9, align: center"]100[/TD]
[TD="bgcolor: #93CDDD, align: center"]80[/TD]
[TD="align: center"]10.0[/TD]
[TD="align: center"]12.5[/TD]
[TD="align: center"]15.0[/TD]
[TD="align: center"]16.5[/TD]
[TD="align: right"][/TD]
[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="bgcolor: #FAFAFA, align: right"]49[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="bgcolor: #FAFAFA, align: right"]65[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]

[TD="bgcolor: #FAFAFA, align: right"]3.0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]

[TD="align: right"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]

[TD="align: right"]4.8[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]

[TD="align: right"]4.8[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]
[TD="bgcolor: #92D050"]Answer[/TD]
[TD="bgcolor: #92D050, align: right"]0.01[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #E0E0F0"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]B19[/TH]
[TD="align: left"]=MIN(OFFSET($C$4,$B$18-1,):OFFSET($F$4,$B$18-1,,))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]B20[/TH]
[TD="align: left"]=IF(B14<B19,B19,B14)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]B21[/TH]
[TD="align: left"]=LOOKUP(B20,OFFSET($C$4,$B$18-1,):OFFSET($F$4,$B$18-1,,),{0.01,0.02,0.03,0.04})[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #E0E0F0"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]B18[/TH]
[TD="align: left"]{=MATCH(1,(A4:A9=B12)*(B4:B9=B13),0)}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
mike&jess said:
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

[TABLE="width: 448"]
<tbody>[TR]
[TD][/TD]
[TD]Pressure
[/TD]
[TD]0.01
[/TD]
[TD]0.02
[/TD]
[TD]0.03
[/TD]
[TD]0.04
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]kvs
[/TD]
[TD] dn
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]31
[/TD]
[TD]50
[/TD]
[TD]3.0
[/TD]
[TD]3.7
[/TD]
[TD]4.4
[/TD]
[TD]5.0
[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]40
[/TD]
[TD]50
[/TD]
[TD]4.0
[/TD]
[TD]4.9
[/TD]
[TD]5.8
[/TD]
[TD]6.4
[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]49
[/TD]
[TD]65
[/TD]
[TD]4.8
[/TD]
[TD]5.9
[/TD]
[TD]7.0
[/TD]
[TD]7.6
[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]63
[/TD]
[TD]65
[/TD]
[TD]6.4
[/TD]
[TD]7.7
[/TD]
[TD]9.0
[/TD]
[TD]10.0
[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]78
[/TD]
[TD]80
[/TD]
[TD]7.6
[/TD]
[TD]9.1
[/TD]
[TD]10.5
[/TD]
[TD]12.0
[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]100
[/TD]
[TD]80
[/TD]
[TD]10.0
[/TD]
[TD]12.5
[/TD]
[TD]15.0
[/TD]
[TD]16.5
[/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

Does dn matter at all? A kvs value seems to be sufficient for finding the row to look at...
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Solved
INDEX MATCH MAXIFS / AGGREGATE - Multiple criteria
Replies
3
Views
529
Fluff
Fluff
skyh3ck
  • Question Question
1st match column header, 2nd lookup a value in same column, 3rd return corresponding value from the very first column ?? need help
Replies
2
Views
637
skyh3ck
skyh3ck
K
  • Question Question
Multiple Column look up and value return using index match
Replies
1
Views
350
Keegan4123
K
C
  • Solved
Create Function that Calculates a Value Relative to Original Price and Also Constructs a Price Ladder
2
Replies
10
Views
1K
NdNoviceHlp
NdNoviceHlp
K
  • Question Question
Question about VBA macro using Index Match with 2 criteria
Replies
9
Views
616
kiri
K

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top