Need Help with lookup values in Table

[cank]

I have a table Like This:
Tube Diameter
15 14 13 12 11 10 9
15,000 60 54 50 45 40 36 32
12,000 45 42 39 35 32 29 26
9,000 33 30 29 26 24 21 18
7,500 26 24 22 21 19 17 15
6,000 20 19 18 16 15 13 12
5,000 17 16 15 12 11 10 8
4,000 13 12 11 10 9 8 7
3,000 10 9 9 8 7 6 5
2,000 7 6 6 5 5 4 4
Transfomer Size

The table data represents the Maximum footage (of Neon Tubing) allowed for a
particular transformer size based on the Tube Diameter.
The information that I want to input is:
1. Tube Diameter
2. Actual Footage

I need to write a Lookup function to do several things.
The first is, for a particular job I will not always have the exact footage
listed in the chart, so I will need to round the actual value up to the
nearest value based on the tube diameter.
I have done this with the HLOOKUP function but I have to manually put in the
Row Index, and is therefore not practical.

Second, I need to Reference or Pick the correct size transformer based on
this rounded-up value and the tube diameter.

I would also like a way to divide the footage if it fell out of the scope of
the chart, i.e. if there was 85 feet determine that I need a 15,000 and a
7,500 transformer.

I can get the Column index with a MATCH Function based on the Tube Size but
I can't seem to get the Row index unles I only search in one column.

Let me know if anyone has some ideas.

Thanks,
cank

[bpeltzer]

I'm not sure I understand the application well enough to answer completely.
But it sounds as if finding the correct transformer size will allow you to
get further along. To that end, if the transformer sizes are all in column A
(and nothing else is there) and the input transformer size is in C14, then
=LARGE(A:A,COUNTIF(A:A,">="&C14)) should return the appropriate size (change
C14 to the cell with your transformer size input, and change A:A, if
necessary, to precisely specify the range, ex: $A$2:$A$10).
The logic is to figure out how many transformers are at least as big as the
requested size, and return that nth biggest value. From there, of course,
you could use the match function to figure out what row you need to look in
(maybe an input for your hlookup?).
HTH. --Bruce

"cank" wrote:

> I have a table Like This:
> Tube Diameter
> 15 14 13 12 11 10 9
> 15,000 60 54 50 45 40 36 32
> 12,000 45 42 39 35 32 29 26
> 9,000 33 30 29 26 24 21 18
> 7,500 26 24 22 21 19 17 15
> 6,000 20 19 18 16 15 13 12
> 5,000 17 16 15 12 11 10 8
> 4,000 13 12 11 10 9 8 7
> 3,000 10 9 9 8 7 6 5
> 2,000 7 6 6 5 5 4 4
> Transfomer Size
>
> The table data represents the Maximum footage (of Neon Tubing) allowed for a
> particular transformer size based on the Tube Diameter.
> The information that I want to input is:
> 1. Tube Diameter
> 2. Actual Footage
>
> I need to write a Lookup function to do several things.
> The first is, for a particular job I will not always have the exact footage
> listed in the chart, so I will need to round the actual value up to the
> nearest value based on the tube diameter.
> I have done this with the HLOOKUP function but I have to manually put in the
> Row Index, and is therefore not practical.
>
> Second, I need to Reference or Pick the correct size transformer based on
> this rounded-up value and the tube diameter.
>
> I would also like a way to divide the footage if it fell out of the scope of
> the chart, i.e. if there was 85 feet determine that I need a 15,000 and a
> 7,500 transformer.
>
> I can get the Column index with a MATCH Function based on the Tube Size but
> I can't seem to get the Row index unles I only search in one column.
>
> Let me know if anyone has some ideas.
>
> Thanks,
> cank

[Dav]

I think the following should work. The assumption is no diameters go beyond 15

[cank]

Bruce,
The transformer size is one one thing I don't know. I will try to explain it
a little more clearly.

If I have 30 feet of Neon and I know the tube size is 15mm, I need to first
evaluate what coulumn I need to be in (Col 2). Then I need to evaluate the
footage (30) and round up to the nearest number(33). From there I know I am
in Col 2:Row 4. I then need to pull out the transformer size on the left that
corrolates to that (9,000).
I hope this Clarifies my problem.

Dav,
The link said "Invalid Attachment specified". Let me know if there is a
different way to look that up.

Thanks for the response.
Cank

[cank]

"cank" wrote:
> Dav,
> The link said "Invalid Attachment specified". Let me know if there is a
> different way to look that up.

Dav,
I found my post on ExcelTip.com and was able to find your link.
That worked, THANKS!
Do you have time to explain it?

Thanks for the help.
cank