|
Getting a value from large table.
Hi, I have a data table where column A = Make (named MAKE), B = Model (named
MODEL), Row 1 = Model Year (named MODELYR). On a different sheet, I have a list of miscellaneous combinations. On this sheet, column A contains the make, B the Model and C the Model Year. How can I get the contents from the table to be populated in column D for my list? I have tried SUMPRODUCT, SUMIFS and INDEX (with MATCH) functions, but continue to get the #Value error. I have also array-entered each of the functions. I would like the value in D1 where MAKE=A1, MODEL=B1 and MODELYR=C1. -- TIA, Brad E. |
Getting a value from large table.
sumproduct should work OK if you do it properly with the same length ranges,
etc. post your formula -- Don Guillett Microsoft MVP Excel SalesAid Software "Brad E." wrote in message ... Hi, I have a data table where column A = Make (named MAKE), B = Model (named MODEL), Row 1 = Model Year (named MODELYR). On a different sheet, I have a list of miscellaneous combinations. On this sheet, column A contains the make, B the Model and C the Model Year. How can I get the contents from the table to be populated in column D for my list? I have tried SUMPRODUCT, SUMIFS and INDEX (with MATCH) functions, but continue to get the #Value error. I have also array-entered each of the functions. I would like the value in D1 where MAKE=A1, MODEL=B1 and MODELYR=C1. -- TIA, Brad E. |
Getting a value from large table.
One way...
=SUMPRODUCT(--(Make=A10),--(Model=B10),INDEX(Retail!C2:G247,,MATCH(C10,MODELY R,0))) -- Biff Microsoft Excel MVP "Brad E." wrote in message ... I would like to use something like =SUMPRODUCT(MAKE=A10,MODEL=B10,MODELYR=C10,Retail! $C$2:$G$247) but MAKE = Retail!$A$2:$A$247 MODEL = Retail!$B$2:$B$247 MODELYR = Retail!$C$1:$G$1 (consists of Model Years 2006 - 2010) and, therefore, the ranges aren't the correct size. -- TIA, Brad E. "Don Guillett" wrote: sumproduct should work OK if you do it properly with the same length ranges, etc. post your formula -- Don Guillett Microsoft MVP Excel SalesAid Software "Brad E." wrote in message ... Hi, I have a data table where column A = Make (named MAKE), B = Model (named MODEL), Row 1 = Model Year (named MODELYR). On a different sheet, I have a list of miscellaneous combinations. On this sheet, column A contains the make, B the Model and C the Model Year. How can I get the contents from the table to be populated in column D for my list? I have tried SUMPRODUCT, SUMIFS and INDEX (with MATCH) functions, but continue to get the #Value error. I have also array-entered each of the functions. I would like the value in D1 where MAKE=A1, MODEL=B1 and MODELYR=C1. -- TIA, Brad E. . |
Getting a value from large table.
Thanks, Biff. Skipping the ROW entry in the INDEX function works great. I
didn't know you could do that. Brad E. "T. Valko" wrote: One way... =SUMPRODUCT(--(Make=A10),--(Model=B10),INDEX(Retail!C2:G247,,MATCH(C10,MODELY R,0))) Biff Microsoft Excel MVP "Brad E." wrote in message ... I would like to use something like =SUMPRODUCT(MAKE=A10,MODEL=B10,MODELYR=C10,Retail! $C$2:$G$247) but MAKE = Retail!$A$2:$A$247 MODEL = Retail!$B$2:$B$247 MODELYR = Retail!$C$1:$G$1 (consists of Model Years 2006 - 2010) and, therefore, the ranges aren't the correct size. -- TIA, Brad E. "Don Guillett" wrote: sumproduct should work OK if you do it properly with the same length ranges, etc. post your formula -- Don Guillett Microsoft MVP Excel SalesAid Software "Brad E." wrote in message ... Hi, I have a data table where column A = Make (named MAKE), B = Model (named MODEL), Row 1 = Model Year (named MODELYR). On a different sheet, I have a list of miscellaneous combinations. On this sheet, column A contains the make, B the Model and C the Model Year. How can I get the contents from the table to be populated in column D for my list? I have tried SUMPRODUCT, SUMIFS and INDEX (with MATCH) functions, but continue to get the #Value error. I have also array-entered each of the functions. I would like the value in D1 where MAKE=A1, MODEL=B1 and MODELYR=C1. -- TIA, Brad E. |
Getting a value from large table.
When you index a 2D range you have to specify both the row and column
argument otherwise, you'll get an error. In this application: INDEX(Retail!C2:G247,,MATCH(C10,MODELYR,0)) The row argument has been omitted so it defaults to 0. Since there isn't a relative row 0 in the range it includes *all* rows within the range. You can either omit the row argument as I did or you can define it as 0: INDEX(Retail!C2:G247,0,MATCH(C10,MODELYR,0)) -- Biff Microsoft Excel MVP "Brad E." wrote in message ... Thanks, Biff. Skipping the ROW entry in the INDEX function works great. I didn't know you could do that. Brad E. "T. Valko" wrote: One way... =SUMPRODUCT(--(Make=A10),--(Model=B10),INDEX(Retail!C2:G247,,MATCH(C10,MODELY R,0))) Biff Microsoft Excel MVP "Brad E." wrote in message ... I would like to use something like =SUMPRODUCT(MAKE=A10,MODEL=B10,MODELYR=C10,Retail! $C$2:$G$247) but MAKE = Retail!$A$2:$A$247 MODEL = Retail!$B$2:$B$247 MODELYR = Retail!$C$1:$G$1 (consists of Model Years 2006 - 2010) and, therefore, the ranges aren't the correct size. -- TIA, Brad E. "Don Guillett" wrote: sumproduct should work OK if you do it properly with the same length ranges, etc. post your formula -- Don Guillett Microsoft MVP Excel SalesAid Software "Brad E." wrote in message ... Hi, I have a data table where column A = Make (named MAKE), B = Model (named MODEL), Row 1 = Model Year (named MODELYR). On a different sheet, I have a list of miscellaneous combinations. On this sheet, column A contains the make, B the Model and C the Model Year. How can I get the contents from the table to be populated in column D for my list? I have tried SUMPRODUCT, SUMIFS and INDEX (with MATCH) functions, but continue to get the #Value error. I have also array-entered each of the functions. I would like the value in D1 where MAKE=A1, MODEL=B1 and MODELYR=C1. -- TIA, Brad E. |
| All times are GMT +1. The time now is 09:10 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com