Formula to extract pricing from a chart
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 |
All times are GMT +1. The time now is 11:06 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com