Chart is on my L-Ups Work Sheet.
Chart consists of (9) rows
(Showing Lengths down left side)
and (7) Columns
(Showing unit codes across Top)

Whatever the Unit Code is in cell E5 of my active sheet,
to trigger a "match" to the Unit Code on my L-Ups Work
Sheet - Then extract (copy) the pricing under that code
and insert into cells J6 thru J14

If the best way is to use a nested Look-up, then I need to know if
there is a function that allows the use of the chart, OR do I have to
split the chart up into 7 pieces of 9 rows.

Somehow I have a feeling I'm missing something obvious, but I
just can't get my head around this one. (not the first time, though)

--
John F. Scholten

Assumptions:

B1:H1 contains your labels (unit codes)
A2:A10 contains your lengths
B2:H10 contains your data
The worksheet called "L-Ups" contains your table

Formula:

On Sheet2...

1) Select J6:J14 (these cells should be highlighted)

2) Enter the following array formula that needs to be entered using
CONTROL+SHIFT+ENTER:
=HLOOKUP(E5,'L-Ups'!B1:H10,{2;3;4;5;6;7;8;9;10},0)

...where E5 is your lookup value on Sheet2. Also, the above formula
can be replaced with the following array formula that also needs to be
entered using CONTROL+SHIFT+ENTER...

=HLOOKUP(E5,'L-Ups'!B1:H10,ROW(INDIRECT("2:10")),0)

Hope this helps!

John F Wrote:
Chart is on my L-Ups Work Sheet.
Chart consists of (9) rows
(Showing Lengths down left side)
and (7) Columns
(Showing unit codes across Top)

Whatever the Unit Code is in cell E5 of my active sheet,
to trigger a "match" to the Unit Code on my L-Ups Work
Sheet - Then extract (copy) the pricing under that code
and insert into cells J6 thru J14

If the best way is to use a nested Look-up, then I need to know if
there is a function that allows the use of the chart, OR do I have to
split the chart up into 7 pieces of 9 rows.

Somehow I have a feeling I'm missing something obvious, but I
just can't get my head around this one. (not the first time, though)

--
John F. Scholten


--
Domenic
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=277533

Had to try both of them.
Both worked great.
Thanks ever.....
John F.

"Domenic" wrote:


Assumptions:

B1:H1 contains your labels (unit codes)
A2:A10 contains your lengths
B2:H10 contains your data
The worksheet called "L-Ups" contains your table

Formula:

On Sheet2...

1) Select J6:J14 (these cells should be highlighted)

2) Enter the following array formula that needs to be entered using
CONTROL+SHIFT+ENTER:
=HLOOKUP(E5,'L-Ups'!B1:H10,{2;3;4;5;6;7;8;9;10},0)

...where E5 is your lookup value on Sheet2. Also, the above formula
can be replaced with the following array formula that also needs to be
entered using CONTROL+SHIFT+ENTER...

=HLOOKUP(E5,'L-Ups'!B1:H10,ROW(INDIRECT("2:10")),0)

Hope this helps!

John F Wrote:
Chart is on my L-Ups Work Sheet.
Chart consists of (9) rows
(Showing Lengths down left side)
and (7) Columns
(Showing unit codes across Top)

Whatever the Unit Code is in cell E5 of my active sheet,
to trigger a "match" to the Unit Code on my L-Ups Work
Sheet - Then extract (copy) the pricing under that code
and insert into cells J6 thru J14

If the best way is to use a nested Look-up, then I need to know if
there is a function that allows the use of the chart, OR do I have to
split the chart up into 7 pieces of 9 rows.

Somehow I have a feeling I'm missing something obvious, but I
just can't get my head around this one. (not the first time, though)

--
John F. Scholten


--
Domenic
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=277533