Lookup Function in Reverse?
 

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

Lookup Function in Reverse?
 

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

On Jan 17, 1:32*am, stogie wrote:
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

Lookup Function in Reverse?
 

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

http://www.mrexcel.com/articles/exce...ndex-match.php
http://www.ozgrid.com/Excel/left-lookup.htm
http://support.microsoft.com/kb/214142
http://skp.mvps.org/xl00002.htm
--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"Pete_UK" wrote:

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

On Jan 17, 1:32 am, stogie wrote:
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

Lookup Function in Reverse?
 

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