# Variable Values with a Lookup Table

#### rcb007

##### Board Regular
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.

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

Replies
9
Views
150
Replies
0
Views
162
Replies
3
Views
219
Replies
9
Views
274
Replies
7
Views
146

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.

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.

### Which adblocker are you using?

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

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