Variable Values with a Lookup Table

rcb007

Board Regular
Joined
Nov 12, 2020
Messages
78
Office Version
  1. 365
Platform
  1. Windows
I am trying to use this lookup table so that it can be dynamic, so I do not have to create separate tables as shown below.
The purpose of the table is to generate a value for H (dia).

It uses the Q Value to be compared to the Full Value to get the desired Dia Value.

In this example (The First Table to the Left),

Q value from Colum E needs to fall in between the values in Column B (min) and Column C (max) to show the Column A (dia) Result.



The Area from I3 through K6 is the work area. I can supply the Q and Slope, but I need the dia value from that.
I did not know if there was a way to use one table instead of creating all these other tables to generate the (dia) Result.


Capture.JPG


thank you for any help and sorry if this seems very confusing. I tried my best to color coordinate between everything.
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

GraH

Well-known Member
Joined
Mar 22, 2020
Messages
1,081
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
It's a pity your data is in a picture.
Maybe this Xlookup works? I could not enter all the data, so I picked a few lines.
Book1
ABCDEFGHIJK
1diaminFullnQSlopeQdiaSlope
21201,780,0135,20,25000,25
3151,783,230,0135,20,255,2180,25
4183,235,250,0135,20,256,2180,5
5184,577,430,0136,20,59,2210,75
6219,113,720,0139,20,7510,2210,75
72413,7219,580,0139,20,75
Sheet1
Cell Formulas
RangeFormula
F2:F7F2=XLOOKUP(A2:A7&"-"&E2:E7,$J$2:$J$6&"-"&$I$2:$I$6,$K$2:$K$6,,-1)
Dynamic array formulas.
 

rcb007

Board Regular
Joined
Nov 12, 2020
Messages
78
Office Version
  1. 365
Platform
  1. Windows
Q Comparison.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZ
1diaminFullnQ SlopediaminFullnQ SlopediaminFullnQ Slope
21201.780.0135.200.25QdiaSlope1202.520.0136.200.501203.090.0139.200.75
3151.783.230.0135.200.2515.2180.25152.524.570.0136.200.50153.095.590.0139.200.75
4183.235.250.0135.200.2526.2180.5184.577.430.0136.200.50185.599.100.0139.200.75
5215.257.920.0135.200.2539.2210.75217.4311.200.0136.200.50219.1013.720.0139.200.75
6247.9211.310.0135.200.25410.2210.752411.2016.000.0136.200.502413.7219.590.0139.200.75
73011.3120.510.0135.200.25LOOKUP(H3,C2:C14,A2:A14)3016.0029.000.0136.200.503019.5935.520.0139.200.75
83620.5133.350.0135.200.253629.0047.160.0136.200.503635.5257.760.0139.200.75
94233.3550.300.0135.200.25QVariable4247.1671.140.0136.200.504257.7687.130.0139.200.75
104850.3071.820.0135.200.25SlopeVariable4871.14101.570.0136.200.504887.13124.400.0139.200.75
115471.8298.320.0135.200.2554101.57139.050.0136.200.5054124.40170.300.0139.200.75
126098.32130.220.0135.200.2560139.05184.160.0136.200.5060170.30225.550.0139.200.75
1366130.22167.900.0135.200.2566184.16237.450.0136.200.5066225.55290.820.0139.200.75
1472167.90211.750.0135.200.2572237.45299.470.0136.200.5072290.82366.770.0139.200.75
15diaminFullnQ SlopediaminFullnQ SlopediaminFullnQ Slope
16
17
Sheet1
Cell Formulas
RangeFormula
C2:C14,W2:W14,P2:P14C2=IF(SUM(A2:A2)=0,"-",(1.486/D2)*((A2^2)*PI()/576)*((A2/48)^(2/3))*((F2/100)^(1/2)))
B3:B14,V3:V14,O3:O14B3=C2
J3J3=LOOKUP(I3,B2:B14,A2:A14)
J4J4=LOOKUP(I4,O2:O14,N2:N14)
J5J5=LOOKUP(I5,V2:V14,U2:U14)
J6J6=LOOKUP(I6,$C$2:$C$14,$A$2:$A$14)
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,822
Messages
5,766,648
Members
425,366
Latest member
Mau15092000

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
Top