Table Lookup?

[agrajag42]

I'm trying to create a function that uses two variables to search a table and return a result.

I have a table with Current (in Amps) down the left side in the first column - from 1 to 25 Amps. The first row is Wire Gauges - 14, 12, 10, 8, 6, 4, 3, 2. The data in the table is the distance (in feet) which you can run that gauge wire with that current, with less than %2 losses.

I'm trying to create a function that supplies "Current" and "Distance" and comes out with the proper gauge to use. I thought maybe nested lookup functions would work, but even if they did it doesn't look like an elegant solution.

Anyone have any ideas?

Thanks!
/Ethan

[Palmetto]

See attached

[ChemistB]

He's looking for wire gauge with distance and amps being known.

[ChemistB]

Here's how I did it. I'm sure that it can be done without the intermediate step of pulling out that specific row but I'm drawing a blank.

1. Arraigning your table. To get this to work properly, the table needs to read decreasing distances from left to right.
2. I set up your inputs B1 = Amps, B2 = Distance, Table is from A5:K30
3. I pulled out the appropriate AMP Row in A32:K32 as follows
A32 = B1
B32 dragged right =

=INDEX($B$6:$K$30,MATCH(A32,A6:A30,0),COLUMN(A1))

Now instead of a multirow table to extract from, you have a single row.
4. In B3, the calculation for wire gauge

=IF(ISERROR(MATCH(B2,B32:K32,-1)),"Outside Range",INDEX($B$5:$K$5,1,MATCH(B2,B32:K32,-1)))

If your outside an allowable range, you'll get "Outside Range"
See attachment and run a few inputs and see if that would work for you.

[WHER]

ChemistB's solution, plus an alternative without the helper row.

[ChemistB]

Very nice WHER.

[agrajag42]

Very, very impressive. This would have taken me a year. Thanks very much to all who helped! I learned a lot trying to figure all that out.

Two things:

1. My table (see below, just before quote) is in ascending order, not descending order. That breaks the MATCH function. I could change the table if I had to - but is there a way around that?

2. I've got one non-integer number in the "Amps" column, and a couple of non-integers in my "current" inputs. Since the OFFSET counts using the "current" (which is brilliant), this would probably break the formula.

Again, a thousand thanks to everyone. I had a lot of fun reading and figuring out the responses.

/Ethan


Amps 20 14 12 10 8 6 4 3
0.5 22.86 76.43 121.21 193.55 308.48 488.80 779.22 979.59
1 11.43 38.22 60.61 96.77 154.24 244.40 389.61 489.80
2 5.71 19.11 30.30 48.39 77.12 122.20 194.81 244.90
3 3.81 12.74 20.20 32.26 51.41 81.47 129.87 163.27
4 2.86 9.55 15.15 24.19 38.56 61.10 97.40 122.45
5 2.29 7.64 12.12 19.35 30.85 48.88 77.92 97.96
6 1.90 6.37 10.10 16.13 25.71 40.73 64.94 81.63
7 1.63 5.46 8.66 13.82 22.03 34.91 55.66 69.97
8 1.43 4.78 7.58 12.10 19.28 30.55 48.70 61.22
9 1.27 4.25 6.73 10.75 17.14 27.16 43.29 54.42
10 1.14 3.82 6.06 9.68 15.42 24.44 38.96 48.98
11 1.04 3.47 5.51 8.80 14.02 22.22 35.42 44.53
12 0.95 3.18 5.05 8.06 12.85 20.37 32.47 40.82
13 0.88 2.94 4.66 7.44 11.86 18.80 29.97 37.68
14 0.82 2.73 4.33 6.91 11.02 17.46 27.83 34.99
15 0.76 2.55 4.04 6.45 10.28 16.29 25.97 32.65
16 0.71 2.39 3.79 6.05 9.64 15.27 24.35 30.61
17 0.67 2.25 3.57 5.69 9.07 14.38 22.92 28.81
18 0.63 2.12 3.37 5.38 8.57 13.58 21.65 27.21
19 0.60 2.01 3.19 5.09 8.12 12.86 20.51 25.78
20 0.57 1.91 3.03 4.84 7.71 12.22 19.48 24.49
21 0.54 1.82 2.89 4.61 7.34 11.64 18.55 23.32
22 0.52 1.74 2.75 4.40 7.01 11.11 17.71 22.26
23 0.50 1.66 2.64 4.21 6.71 10.63 16.94 21.30
24 0.48 1.59 2.53 4.03 6.43 10.18 16.23 20.41
25 0.46 1.53 2.42 3.87 6.17 9.78 15.58 19.59

My

ChemistB's solution, plus an alternative without the helper row.