Lookup Function in Reverse?

[stogie]

Hey group, I am an FNG to the group but have experience with Excel,
but I have hit a new snag. I am using Office
2007. I am familiar with Lookup, less so with H/VLookup, but I have
what seems to be a reverse operation. At work
we have a number of tables for a variety data and we are working on a
"universal" spreadsheet, so current data has
to stay in the same format.

This said, the table I am working with now has two headers. The one
down the side lists wire gauge. The one
across the top lists conduit size, as is separated vertically from the
data field I am working with. This data field
contains quantity of wires per the size header that can fit into a
conduit size noted per top header. I have the wire gauge as a variable
and the wire quantity as a constant. I need to find the corresponding
row, find a cell equal to or
greater than the constant, and return the column header. Can this be
done?

Thanks in advance,
Stogie

 

[Pete_UK]

It's late here and I'm having difficulty visualising what you have, so
it would be a good idea to post an example of the layout of your
table. Give details, also, of what columns/cells you are using.

However, I think you can do what you want by using an INDEX/MATCH
combination.

Hope this helps.

Pete

 

[JBeaucaire]

INDEX(MATCH) seems to be what you're after. Lot's to read about it here:

http://www.mrexcel.com/articles/excel-vlookup-index-match.php
http://www.ozgrid.com/Excel/left-lookup.htm
http://support.microsoft.com/kb/214142
http://skp.mvps.org/xl00002.htm

 

[stogie]

Pete
Here is the basic table:
Wire Ga 8 (Variable)
Wire Qty 4 (Constant) Conductor Size (AWG/kcmil) Trade Size (Metric
Designator)
Conduit Size ? ½ (16) ¾ (21) 1 (27) 1¼ (35) 1½ (41) 2 (53) 2½ (63)
3 (78) 3½ (91) 4 (103)

12 9 16 26 45 61 101 176 266 347 443
10 5 10 16 28 38 63 111 167 219 279
8 3 6 9 16 22 36 64 96 126 161
6 2 4 7 12 16 26 46 69 91 116
4 1 2 4 7 10 16 28 43 56 71
3 1 1 3 6 8 13 24 36 47 60
2 1 1 3 5 7 11 20 30 40 51
1 1 1 1 4 5 8 15 22 29 37
1/0 1 1 1 3 4 7 12 19 25 32
2/0 0 1 1 2 3 6 10 16 20 26
3/0 0 1 1 1 3 5 8 13 17 22
4/0 0 1 1 1 2 4 7 11 14 18
250 0 0 1 1 1 3 6 9 11 15
300 0 0 1 1 1 3 5 7 10 13
350 0 0 1 1 1 2 4 6 9 11
400 0 0 0 1 1 1 4 6 8 10
500 0 0 0 1 1 1 3 5 6 8
600 0 0 0 1 1 1 2 4 5 7
700 0 0 0 1 1 1 2 3 4 6
750 0 0 0 0 1 1 1 3 4 5
800 0 0 0 0 1 1 1 3 4 5
900 0 0 0 0 1 1 1 3 3 4
1000 0 0 0 0 1 1 1 2 3 4


Wire size is down the left starting with 12. This is the variable that
is searched for. The constant is 4 and has to match of be greater than
what is listed in the field.
The returned value is at the top.

JB, I will check your links.
Thank y'all
Stogie